🛢️ Unit 2 – Database Query using SQL · Lesson 05
Operations on Relations & Joins
Two club tables
CHESS and QUIZ store students in two school clubs. Both have the same columns, so set operations can be used on them.
SQL
SELECT * FROM CHESS;
Output
+--------+--------+-------+ | RollNo | Name | Class | +--------+--------+-------+ | 11 | Arnav | XII | | 14 | Bhavya | XII | | 17 | Charu | XI | | 21 | Daksh | XI | +--------+--------+-------+
SQL
SELECT * FROM QUIZ;
Output
+--------+--------+-------+ | RollNo | Name | Class | +--------+--------+-------+ | 14 | Bhavya | XII | | 19 | Esha | XII | | 21 | Daksh | XI | +--------+--------+-------+
UNION
UNION gives rows that are in either table; duplicates appear once.
SQL
SELECT Name FROM CHESS UNION SELECT Name FROM QUIZ ORDER BY Name;
Output
+--------+ | Name | +--------+ | Arnav | | Bhavya | | Charu | | Daksh | | Esha | +--------+
INTERSECT
INTERSECT gives rows common to both tables – students in both clubs.
SQL
SELECT Name FROM CHESS INTERSECT SELECT Name FROM QUIZ ORDER BY Name;
Output
+--------+ | Name | +--------+ | Bhavya | | Daksh | +--------+
SQL
SELECT Name FROM CHESS WHERE RollNo IN (SELECT RollNo FROM QUIZ);
Output
+--------+ | Name | +--------+ | Bhavya | | Daksh | +--------+
MINUS / EXCEPT
MINUS (EXCEPT in MySQL) gives rows of the first table that are not in the second – students only in Chess.
SQL
SELECT Name FROM CHESS EXCEPT SELECT Name FROM QUIZ ORDER BY Name;
Output
+-------+ | Name | +-------+ | Arnav | | Charu | +-------+
SQL
SELECT Name FROM CHESS WHERE RollNo NOT IN (SELECT RollNo FROM QUIZ);
Output
+-------+ | Name | +-------+ | Arnav | | Charu | +-------+
Cartesian product
Listing two tables in FROM without a join condition pairs every row of one with every row of the other: 4 × 3 = 12 rows.
SQL
SELECT COUNT(*) FROM CHESS, QUIZ;
Output
+----------+ | COUNT(*) | +----------+ | 12 | +----------+
SQL
SELECT C.Name, Q.Name FROM CHESS C, QUIZ Q WHERE C.Class = 'XI' AND Q.Class = 'XII' ORDER BY C.Name, Q.Name;
Output
+-------+--------+ | Name | Name | +-------+--------+ | Charu | Bhavya | | Charu | Esha | | Daksh | Bhavya | | Daksh | Esha | +-------+--------+
Using two relations: joins
SQL
SELECT * FROM SUPPLIER;
Output
+-------+---------------+--------+ | SupID | SupName | City | +-------+---------------+--------+ | S1 | Kiran Traders | Delhi | | S2 | Om Supplies | Indore | | S3 | Star Mart | Kochi | +-------+---------------+--------+
SQL
SELECT * FROM PRODUCT;
Output
+-----+------------+-------+-------+ | PID | PName | Price | SupID | +-----+------------+-------+-------+ | 1 | Pencil Box | 120 | S1 | | 2 | Crayons | 80 | S2 | | 3 | Stapler | 150 | S1 | | 4 | Glue Stick | 35 | S3 | +-----+------------+-------+-------+
The condition PRODUCT.SupID = SUPPLIER.SupID keeps only matching pairs – an equi-join.
SQL
SELECT PName, Price, SupName FROM PRODUCT, SUPPLIER WHERE PRODUCT.SupID = SUPPLIER.SupID ORDER BY PName;
Output
+------------+-------+---------------+ | PName | Price | SupName | +------------+-------+---------------+ | Crayons | 80 | Om Supplies | | Glue Stick | 35 | Star Mart | | Pencil Box | 120 | Kiran Traders | | Stapler | 150 | Kiran Traders | +------------+-------+---------------+
SQL
SELECT P.PName, S.City FROM PRODUCT AS P JOIN SUPPLIER AS S ON P.SupID = S.SupID WHERE P.Price > 100 ORDER BY P.PName;
Output
+------------+-------+ | PName | City | +------------+-------+ | Pencil Box | Delhi | | Stapler | Delhi | +------------+-------+
SQL
SELECT PName, SupName FROM PRODUCT NATURAL JOIN SUPPLIER ORDER BY PName;
Output
+------------+---------------+ | PName | SupName | +------------+---------------+ | Crayons | Om Supplies | | Glue Stick | Star Mart | | Pencil Box | Kiran Traders | | Stapler | Kiran Traders | +------------+---------------+
Join with GROUP BY
SQL
SELECT S.SupName, COUNT(*) AS Items, SUM(P.Price) AS Total FROM PRODUCT P, SUPPLIER S WHERE P.SupID = S.SupID GROUP BY S.SupName ORDER BY S.SupName;
Output
+---------------+-------+-------+ | SupName | Items | Total | +---------------+-------+-------+ | Kiran Traders | 2 | 270 | | Om Supplies | 1 | 80 | | Star Mart | 1 | 35 | +---------------+-------+-------+
Rules for set operations
- Same number of columns in both SELECTs, in the same order, with compatible types.
- Column names in the result come from the first SELECT.
- UNION removes duplicates; UNION ALL keeps them.
Frequently Asked Questions
What is required for UNION, INTERSECT and MINUS?
Both SELECT statements must return the same number of columns with compatible data types (union-compatible relations).
Does MySQL support MINUS?
MySQL 8.0.31 and later support EXCEPT (the SQL name for MINUS) and INTERSECT. In older versions use NOT IN and IN with a sub-query, shown on this page.
What is the degree and cardinality of a Cartesian product?
Degree = sum of the degrees of the two tables; cardinality = product of their cardinalities.
