A two-table question is recognisable at a glance: it wants something from one table and something from the other in the same row — an employee’s name beside their department’s name. That needs a join, and papers ask for it in three different notations.
1First, what happens without a condition
Name two tables and give no condition, and you get every row of the first paired with every row of the second. Twelve employees times four departments is forty-eight rows — almost all of them nonsense.
MySQL command line client
mysql> select count(*) from emp, dept;
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)
Restricting it to two employees makes the shape visible. Each of the two is paired with all four departments:
MySQL command line client
mysql> select e.name, d.dname from emp e, dept d where e.empno in (101, 102);
+-------------+----------+
| name | dname |
+-------------+----------+
| Bina Sharma | Sales |
| Arun Thapa | Sales |
| Bina Sharma | Accounts |
| Arun Thapa | Accounts |
| Bina Sharma | Despatch |
| Arun Thapa | Despatch |
| Bina Sharma | Research |
| Arun Thapa | Research |
+-------------+----------+
8 rows in set (0.00 sec)
⚠️The forgotten condition
Two rows times four rows is eight, and only two of the eight are true: Arun Thapa really is in Sales, Bina Sharma really is in Accounts. The other six are the database dutifully pairing things that have nothing to do with each other. Leaving the join condition out of a query is the single most expensive slip in this topic — the query runs, so there is no error to warn you.
2The equi-join — the WHERE form
1
Display the name and salary of each employee along with the name of the department they work in.
2 marks
▶Show the answer
MySQL command line client
mysql> select emp.name, emp.salary, dept.dname from emp, dept where emp.deptno = dept.deptno;
+---------------+----------+----------+
| name | salary | dname |
+---------------+----------+----------+
| Arun Thapa | 58000.00 | Sales |
| Chandan Rai | 31000.00 | Sales |
| Farida Khan | 29500.00 | Sales |
| Ishan Pradhan | 25500.00 | Sales |
| Bina Sharma | 26000.00 | Accounts |
| Emil Lepcha | 47000.00 | Accounts |
| Hema Subba | 49500.00 | Accounts |
| Karan Bhutia | 61000.00 | Accounts |
| Deepa Nair | 54000.00 | Despatch |
| Gopal Das | 24000.00 | Despatch |
| Jyoti Tamang | 33000.00 | Despatch |
+---------------+----------+----------+
11 rows in set (0.01 sec)
Eleven rows, not twelve. Lata Gurung has no deptno, and NULL matches nothing — not even the NULL in another row — so she is left out. A join keeps only the rows that pair up.
deptno exists in both tables, so it must be written as emp.deptno or dept.deptno. Leaving the table off an ambiguous column is an error, and a common one.
3The same join, written JOIN … ON
The newer notation puts the join condition in its own clause instead of mixing it in with the filters. It is the same work and the same answer:
MySQL command line client
mysql> select emp.name, dept.dname from emp join dept on emp.deptno = dept.deptno;
+---------------+----------+
| name | dname |
+---------------+----------+
| Arun Thapa | Sales |
| Chandan Rai | Sales |
| Farida Khan | Sales |
| Ishan Pradhan | Sales |
| Bina Sharma | Accounts |
| Emil Lepcha | Accounts |
| Hema Subba | Accounts |
| Karan Bhutia | Accounts |
| Deepa Nair | Despatch |
| Gopal Das | Despatch |
| Jyoti Tamang | Despatch |
+---------------+----------+
11 rows in set (0.00 sec)
💡Use whichever you prefer
Both forms are correct and both earn full marks. The where form is what most Indian textbooks print; join … on keeps the join condition separate from the row filters, which is easier to read once a query has both. Pick the one you can write without hesitating and stay with it.
4A join with a condition of its own
2
Display the employee name, department name and location for everybody working in a department located in Gangtok.
3 marks
▶Show the answer
MySQL command line client
mysql> select emp.name, dept.dname, dept.location from emp, dept where emp.deptno = dept.deptno and dept.location = 'Gangtok';
+---------------+----------+----------+
| name | dname | location |
+---------------+----------+----------+
| Arun Thapa | Sales | Gangtok |
| Chandan Rai | Sales | Gangtok |
| Farida Khan | Sales | Gangtok |
| Ishan Pradhan | Sales | Gangtok |
| Deepa Nair | Despatch | Gangtok |
| Gopal Das | Despatch | Gangtok |
| Jyoti Tamang | Despatch | Gangtok |
+---------------+----------+----------+
7 rows in set (0.00 sec)
Two conditions joined by and: the first pairs the tables up, the second filters the result. Notice this is the department’s location, not the employee’s city — Chandan Rai lives in Singtam but works for a Gangtok department.
5Shortening it with table aliases
Writing emp. and dept. in front of every column gets long. A one-letter alias after the table name in from can be used everywhere else in the query.
3
Using table aliases, display the name and department name of employees earning more than 45000.
2 marks
▶Show the answer
MySQL command line client
mysql> select e.name, d.dname from emp e, dept d where e.deptno = d.deptno and e.salary > 45000;
+--------------+----------+
| name | dname |
+--------------+----------+
| Arun Thapa | Sales |
| Deepa Nair | Despatch |
| Emil Lepcha | Accounts |
| Hema Subba | Accounts |
| Karan Bhutia | Accounts |
+--------------+----------+
5 rows in set (0.00 sec)
emp e means “call this table e for the rest of the query”. The resultset headings still show the plain column names.
6Natural join
A natural join finds the commonly-named column by itself, joins on it, and — the detail papers test — does not repeat it. The shared deptno appears once, at the front:
Eleven columns: eight from emp plus four from dept is twelve, minus the one that would have been printed twice. Compare with a cartesian product, where deptno would appear in both halves.
7Grouping a joined result
4
Display each department’s name, the number of employees in it and its highest salary.
3 marks
▶Show the answer
MySQL command line client
mysql> select d.dname, count(*), max(e.salary) from emp e, dept d where e.deptno = d.deptno group by d.dname;
+----------+----------+---------------+
| dname | count(*) | max(e.salary) |
+----------+----------+---------------+
| Sales | 4 | 58000.00 |
| Accounts | 4 | 61000.00 |
| Despatch | 3 | 54000.00 |
+----------+----------+---------------+
3 rows in set (0.00 sec)
Three departments, not four. Research has nobody in it, so the join produced no rows for it and there is no group to report. A question that wants empty departments listed too needs an outer join, which is beyond the Class 12 syllabus.
✏️ Quick Check
Two tables have 12 and 4 rows. How many rows does 'select * from a, b;' return?
✏️ Quick Check
Why does the equi-join of emp and dept return 11 rows when emp has 12?
✏️ Quick Check
What does a NATURAL JOIN do that an equi-join written with where does not?