Joins in SQL

Commonly used JOINs are the INNER and OUTER join. An inner join returns records from two tables that satisfy the stated logical relationship stated either explicitly or implicitly. An outer join returns the same result set plus the rows from one table that lack a match in the second table.

The Employees table and the Departments table will be used to illustrate various JOINs. In the Employees table, note that Charles Polk is assigned to department 37 for which there is no entry in the Departments table. Also note that no employees are assigned to the Accounts Receivable department. An inner join omits these entries from the results. An outer join includes rows lacking a match.

mysql> select * from Employees;
+------+--------------+--------+
| id   | name         | deptid |
+------+--------------+--------+
|   21 | Paul O'Neil  |     31 | 
|   22 | Joe Jones    |     33 | 
|   23 | Pam Smith    |     34 | 
|   24 | Betty Benson |     32 | 
|   25 | Charles Polk |     37 |
+------+--------------+--------+

mysql> select * from Departments;
+------+---------------------+
| id   | name                |
+------+---------------------+
|   31 | Administration      | 
|   32 | Clerical            | 
|   33 | Sales               | 
|   34 | Engineering         | 
|   35 | Accounts Receivable | 
+------+---------------------+

Explicit Inner Join (Using JOIN Keyword)

mysql> select * from Employees E inner join Departments D on E.deptid=D.id;
+------+--------------+--------+------+----------------+
| id   | name         | deptid | id   | name           |
+------+--------------+--------+------+----------------+
|   21 | Paul O'Neil  |     31 |   31 | Administration | 
|   24 | Betty Benson |     32 |   32 | Clerical       | 
|   22 | Joe Jones    |     33 |   33 | Sales          | 
|   23 | Pam Smith    |     34 |   34 | Engineering    | 
+------+--------------+--------+------+----------------+

Implict Inner Join

mysql> select * from Employees E, Departments D where E.deptid=D.id;
+------+--------------+--------+------+----------------+
| id   | name         | deptid | id   | name           |
+------+--------------+--------+------+----------------+
|   21 | Paul O'Neil  |     31 |   31 | Administration | 
|   24 | Betty Benson |     32 |   32 | Clerical       | 
|   22 | Joe Jones    |     33 |   33 | Sales          | 
|   23 | Pam Smith    |     34 |   34 | Engineering    | 
+------+--------------+--------+------+----------------+

Outer Joins

An outer join is similar to an inner join except that records are included in the result set where no match exists in the matching criteria. A left outer join returns all records in the first table named in the join (not excluded by the WHERE clause) plus matches or NULL values for fields in the second file where there is no match. A right outer join returns all records in the second table (not excluded by criteria in the WHERE clause) plus matching values in the first table or NULLs where there is no match.

mysql> select * from Employees E left outer join Departments D on E.deptid=D.id;
+------+--------------+--------+------+----------------+
| id   | name         | deptid | id   | name           |
+------+--------------+--------+------+----------------+
|   21 | Paul O'Neil  |     31 |   31 | Administration | 
|   22 | Joe Jones    |     33 |   33 | Sales          | 
|   23 | Pam Smith    |     34 |   34 | Engineering    | 
|   24 | Betty Benson |     32 |   32 | Clerical       | 
|   25 | Charles Polk |     37 | NULL | NULL           | 
+------+--------------+--------+------+----------------+

mysql> select * from Employees E right outer join Departments D on E.deptid=D.id;
+------+--------------+--------+------+---------------------+
| id   | name         | deptid | id   | name                |
+------+--------------+--------+------+---------------------+
|   21 | Paul O'Neil  |     31 |   31 | Administration      | 
|   24 | Betty Benson |     32 |   32 | Clerical            | 
|   22 | Joe Jones    |     33 |   33 | Sales               | 
|   23 | Pam Smith    |     34 |   34 | Engineering         | 
| NULL | NULL         |   NULL |   35 | Accounts Receivable | 
+------+--------------+--------+------+---------------------+

Now, after adding a new column to the Employees table, let's summarize by Department and sex. First, what does the new data look like?

mysql> select * from Employees E join Departments D on E.deptid=D.id;
+------+-----------------+--------+------+------+----------------+
| id   | name            | deptid | Sex  | id   | name           |
+------+-----------------+--------+------+------+----------------+
|   21 | Paul O'Neil     |     31 | M    |   31 | Administration | 
|   22 | Joe Jones       |     33 | M    |   33 | Sales          | 
|   23 | Pam Smith       |     34 | F    |   34 | Engineering    | 
|   24 | Betty Benson    |     32 | F    |   32 | Clerical       | 
|   25 | Patty Pace      |     31 | F    |   31 | Administration | 
|   26 | Edward Jones    |     34 | M    |   34 | Engineering    | 
|   27 | Jimmy Doolittle |     33 | M    |   33 | Sales          | 
+------+-----------------+--------+------+------+----------------+

Now, to summarize the number of men and women working in each department:

mysql> select D.name, sum(case when E.sex='M' then 1 else 0 end) AS GUYS, 
sum(case when E.sex='F' then 1 else 0 end) AS GALS 
from Employees E join Departments D on E.deptid=d.id 
group by D.name;
+----------------+------+------+
| name           | GUYS | GALS |
+----------------+------+------+
| Administration |    1 |    1 | 
| Clerical       |    0 |    1 | 
| Engineering    |    1 |    1 | 
| Sales          |    2 |    0 | 
+----------------+------+------+