In SQL, the UNION operator is used to combine the results of two or more SELECT queries into a single result set. The key thing to remember is that each SELECT statement within the UNION must have the same number of columns, with similar data types in the correspoding columns.
SQL Union Syntax
SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2;
SQL Union Basic Example
This query will return a list of distinct first names from both Patients and Doctors tables.
SELECT first_name FROM Patients UNION SELECT first_name FROM Doctors;
SQL Union ALL Example
This query returns all the first names, including duplicates, from both tables. For example, if a first name appears in both tables, it will be listed twice.
SELECT first_name FROM Patients UNION ALL SELECT first_name FROM Doctors;
SQL Union with WHERE Example
This query will return a list of distinct first names, combining female patients from the Patients table and pediatric doctors from the Doctors table.
SELECT first_name FROM Patients WHERE gender = 'F' UNION SELECT first_name FROM Doctors WHERE specialty = 'Pediatrics';
SQL Union ALL with WHERE Example
This query returns all male patients and all doctors who specialize in cardiology. It will include duplicates if a first name appears in both groups.
SELECT first_name FROM Patients WHERE gender = 'M' UNION ALL SELECT first_name FROM Doctors WHERE specialty = 'Cardiology';
SQL Union Another Union Example
This query combines last names from the Patients and Doctors tables and names from the Students table.
SELECT last_name FROM Patients UNION SELECT last_name FROM Doctors UNION SELECT name FROM Students;
