Learning of SQL Day 24

 

Day 24: UNION and UNION ALL

Introduction:

• The UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements. The UNION operator removes duplicate records, while UNION ALL includes duplicates.

Key Concepts:

• UNION: Combines the result sets of two or more SELECT statements and removes duplicates.

• UNION ALL: Combines the result sets of two or more SELECT statements and includes duplicates.

• Column Matching: The SELECT statements must have the same number of columns in the same order, with compatible data types.

SQL Commands and Examples:

1. Basic UNION:

   • Combine rows from two SELECT statements and remove duplicates.

sql

-- Combine the names of students and teachers without duplicates
SELECT FirstName, LastName FROM Students
UNION
SELECT FirstName, LastName FROM Teachers;

2. UNION ALL:

   • Combine rows from two SELECT statements and include duplicates.

sql

-- Combine the names of students and teachers including duplicates
SELECT FirstName, LastName FROM Students
UNION ALL
SELECT FirstName, LastName FROM Teachers;

3. Combining Different Columns:

   • Ensure the SELECT statements have the same number of columns in the same order.

sql

-- Combine student names and course names as a single result set
SELECT FirstName, LastName FROM Students
UNION
SELECT CourseName, '' FROM Courses;

Practice Exercise:

1. Use the Students and Courses tables.

2. Create a table named Teachers with columns TeacherID, FirstName, LastName, CourseID.

3. Write a query to union the names of students and teachers without duplicates.

4. Write a query to union all the names of students and teachers including duplicates.

5. Write a query to union the first names of students and course names.

sql

-- Create the Teachers table
CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    CourseID INT
);

-- Insert sample data into the Teachers table
INSERT INTO Teachers (TeacherID, FirstName, LastName, CourseID)
VALUES
(1, 'John', 'Doe', 101),
(2, 'Jane', 'Smith', 102);

-- Union the names of students and teachers without duplicates
SELECT FirstName, LastName FROM Students
UNION
SELECT FirstName, LastName FROM Teachers;

-- Union all the names of students and teachers including duplicates
SELECT FirstName, LastName FROM Students
UNION ALL
SELECT FirstName, LastName FROM Teachers;

-- Union the first names of students and course names
SELECT FirstName, '' AS LastName FROM Students
UNION
SELECT CourseName AS FirstName, '' AS LastName FROM Courses;

Understanding the UNION and UNION ALL operators allows you to combine result sets effectively, providing more flexible and comprehensive queries.