🗄️ Unit 3 – Database & SQL · Lesson 23
Joins & Set Operations
Why join tables?
A good database keeps data in separate related tables (to avoid repetition). To answer “which department and floor does Pari work on?” we must combine rows of two tables – this is a join.
Sample tables
SQL
SELECT * FROM DEPT;
Output
+-------+-------+-------+ | DCode | DName | Floor | +-------+-------+-------+ | D1 | Sales | 1 | | D2 | IT | 3 | | D3 | HR | 2 | +-------+-------+-------+
SQL
SELECT * FROM STAFF;
Output
+-----+-------+--------+-------+ | SID | SName | Salary | DCode | +-----+-------+--------+-------+ | 1 | Neel | 40000 | D1 | | 2 | Ojas | 65000 | D2 | | 3 | Pari | 52000 | D2 | | 4 | Qasim | 38000 | D3 | +-----+-------+--------+-------+
Cartesian product
Listing two tables in FROM without a condition pairs every row of the first with every row of the second. Rows = 4 × 3 = 12; degree = 4 + 3 = 7. Most pairs are meaningless.
SQL
SELECT SName, DName FROM STAFF, DEPT ORDER BY SName, DName;
Output
+-------+-------+ | SName | DName | +-------+-------+ | Neel | HR | | Neel | IT | | Neel | Sales | | Ojas | HR | | Ojas | IT | | Ojas | Sales | | Pari | HR | | Pari | IT | | Pari | Sales | | Qasim | HR | | Qasim | IT | | Qasim | Sales | +-------+-------+
SQL
SELECT COUNT(*) AS 'Rows in STAFF x DEPT' FROM STAFF, DEPT;
Output
+----------------------+ | Rows in STAFF x DEPT | +----------------------+ | 12 | +----------------------+
Equi-join
Add a condition that the common column is equal. Use table.column when a column name exists in both tables.
SQL
SELECT SName, Salary, DName FROM STAFF, DEPT WHERE STAFF.DCode = DEPT.DCode ORDER BY SName;
Output
+-------+--------+-------+ | SName | Salary | DName | +-------+--------+-------+ | Neel | 40000 | Sales | | Ojas | 65000 | IT | | Pari | 52000 | IT | | Qasim | 38000 | HR | +-------+--------+-------+
JOIN … ON and aliases
Short aliases (S, D) make queries shorter. JOIN … ON is the modern way of writing the same equi-join.
SQL
SELECT S.SName, D.DName, D.Floor FROM STAFF S JOIN DEPT D ON S.DCode = D.DCode WHERE D.Floor > 1 ORDER BY S.SName;
Output
+-------+-------+-------+ | SName | DName | Floor | +-------+-------+-------+ | Ojas | IT | 3 | | Pari | IT | 3 | | Qasim | HR | 2 | +-------+-------+-------+
Natural join
NATURAL JOIN automatically joins on columns with the same name and shows the common column once.
SQL
SELECT * FROM STAFF NATURAL JOIN DEPT ORDER BY SID;
Output
+-------+-----+-------+--------+-------+-------+ | DCode | SID | SName | Salary | DName | Floor | +-------+-----+-------+--------+-------+-------+ | D1 | 1 | Neel | 40000 | Sales | 1 | | D2 | 2 | Ojas | 65000 | IT | 3 | | D2 | 3 | Pari | 52000 | IT | 3 | | D3 | 4 | Qasim | 38000 | HR | 2 | +-------+-----+-------+--------+-------+-------+
Join with GROUP BY
SQL
SELECT D.DName, COUNT(*) AS Staff, SUM(S.Salary) AS Total FROM STAFF S, DEPT D WHERE S.DCode = D.DCode GROUP BY D.DName ORDER BY D.DName;
Output
+-------+-------+--------+ | DName | Staff | Total | +-------+-------+--------+ | HR | 1 | 38000 | | IT | 2 | 117000 | | Sales | 1 | 40000 | +-------+-------+--------+
Set operations
Set operations combine the results of two SELECTs with the same number and type of columns.
SQL
SELECT Name FROM CLUB_A UNION SELECT Name FROM CLUB_B;
Output
+-------+ | Name | +-------+ | Asha | | Ravi | | Neha | | Zoya | | Kabir | +-------+
SQL
SELECT Name FROM CLUB_A UNION ALL SELECT Name FROM CLUB_B;
Output
+-------+ | Name | +-------+ | Asha | | Ravi | | Neha | | Ravi | | Zoya | | Neha | | Kabir | +-------+
MySQL 8.0.31+ also has INTERSECT and EXCEPT. The portable way, shown below, uses IN and NOT IN.
SQL
SELECT Name FROM CLUB_A WHERE Name IN (SELECT Name FROM CLUB_B);
Output
+------+ | Name | +------+ | Ravi | | Neha | +------+
SQL
SELECT Name FROM CLUB_A WHERE Name NOT IN (SELECT Name FROM CLUB_B);
Output
+------+ | Name | +------+ | Asha | +------+
Practice
- Table A has 5 rows and 3 columns, B has 4 rows and 2 columns. Degree and cardinality of A × B? Degree 5, cardinality 20.
- Show staff names with their floor for staff earning more than 50000. SELECT SName, Floor FROM STAFF S, DEPT D WHERE S.DCode = D.DCode AND Salary > 50000;
Frequently Asked Questions
How many rows does a Cartesian product of tables with 4 and 3 rows give?
4 × 3 = 12 rows, and the number of columns is the sum of the columns of both tables.
What is the difference between an equi-join and a natural join?
An equi-join uses = on the common column and shows that column twice (if selected with *). A natural join joins on all same-named columns automatically and shows the common column only once.
What is the difference between UNION and UNION ALL?
UNION removes duplicate rows from the combined result; UNION ALL keeps them.
