Set Operations


Set operations in SQL allow you to combine the results of two or more queries into a single result set. These operations include UNION, UNION ALL, INTERSECT, and EXCEPT (or MINUS in some SQL dialects). Each of these operations has its specific use case and behavior:

UNION: Combines the results of two or more SELECT queries, removing duplicate rows.
UNION ALL: Combines the results of two or more SELECT queries, including all duplicates.
INTERSECT: Returns only the rows that are common to the results of two SELECT queries.
EXCEPT (or MINUS): Returns the rows from the first SELECT query that are not in the result of the second SELECT query.

Implementing Set Operations in MySQL

UNION:
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION ALL:
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

INTERSECT: 
(MySQL does not support INTERSECT directly, but it can be simulated using INNER JOIN or subqueries)

SELECT column1, column2 FROM table1
WHERE (column1, column2) IN (
    SELECT column1, column2 FROM table2
);

EXCEPT:
MySQL does not support EXCEPT directly, but it can be simulated using a LEFT JOIN or subqueries with NOT IN

SELECT column1, column2 FROM table1
WHERE (column1, column2) NOT IN (
    SELECT column1, column2 FROM table2
);



let's create a complete example including the table schema and set operations in MySQL.
Step 1: Create Tables and Insert Data

First, we'll create two tables, employees and contractors, and insert some sample data into them.
-- Create employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Create contractors table
CREATE TABLE contractors (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Insert data into employees table
INSERT INTO employees (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- Insert data into contractors table
INSERT INTO contractors (id, name) VALUES
(2, 'Bob'),
(4, 'Dave'),
(5, 'Eve');


Step 2: Perform Set Operations
UNION Example

Combines the results of both tables, removing duplicates.
SELECT name FROM employees
UNION
SELECT name FROM contractors;

name                                              
--------------------------------------------------
Alice                                             
Bob                                               
Charlie                                           
Dave                                              
Eve
UNION ALL Example

Combines the results of both tables, including duplicates.
SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

name                                              
--------------------------------------------------
Alice                                             
Bob                                               
Charlie                                           
Bob                                               
Dave                                              
Eve

INTERSECT Example (using INNER JOIN)

Finds the names that are common to both tables.
SELECT e.name 
FROM employees e
INNER JOIN contractors c ON e.id = c.id;

name                                              
--------------------------------------------------
Bob

EXCEPT Example (using LEFT JOIN)

Finds names that are in the employees table but not in the contractors table.
SELECT e.name 
FROM employees e
LEFT JOIN contractors c ON e.id = c.id
WHERE c.id IS NULL;

name                                              
--------------------------------------------------
Alice                                             
Charlie
Alternatively, using NOT IN:
SELECT name 
FROM employees
WHERE id NOT IN (SELECT id FROM contractors);

name                                              
--------------------------------------------------
Alice                                             
Charlie

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more