How to join two SELECT queries

Question

/* Process 01 */
Select Fld1, Fld2, Fld3, Fld4
From Table01
Where xDate = convert(date, '20240101', 112) as P01

/* Process 02 */
Select Fld1, Fld2, Fld3, Fld4, 1 as Late
From Table01
Where xDate < Convert(date, '20241201', 112) as P02
    
-- Combined Process

In Combine Process, I want to join P01 & P02 queries. How can I join two queries given above?

Answer 1

Here are two queries using a UNION or UNION ALL

/* Combine Process */
SELECT Fld1, Fld2, Fld3, Fld4, NULL AS Late
FROM Table01
WHERE xDate = CONVERT(date, '20240101', 112)

UNION ALL

SELECT Fld1, Fld2, Fld3, Fld4, 1 AS Late
FROM Table01
WHERE xDate < CONVERT(date, '20241201', 112);

Please find below the difference between UNION vs UNION ALL:

• Use UNION if you want to remove duplicate rows.
• Use UNION ALL to include all rows, even if duplicates exist. This typically performs better than UNION so should be used if practical.

Output:

Fld1	Fld2	Fld3	Fld4	Late
1	A	X	P	NULL
2	B	Y	Q	1