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 table1UNIONSELECT 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 PatientsUNIONSELECT 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 PatientsUNION ALLSELECT 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'UNIONSELECT 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 ALLSELECT 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 PatientsUNIONSELECT last_name FROM DoctorsUNIONSELECT name FROM Students;
SQL Union Visual Diagram
SQL Union Labs
