What is a SQL Join Statement?

For this guide we’ll discuss the JOIN section of the SQL statement. We will cover its syntax and the different types of joins that SQL enables.

SQL syntax with focus on Join

SELECT col1, col2, col3, etc....
FROM  tableNameOne AS a
JOIN tableNameTwo AS b ON a.primeKey = b.primeKey 
etc...

The JOIN statement could be just JOIN or INNER JOIN, which are the same, or LEFT JOIN (described below).

Different Types of JOINs

  • (INNER) JOIN
  • Return records that have matching values in both tables
  • LEFT (OUTER) JOIN
  • Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN
  • Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN
  • Return all records when there is a match in either left or right table

Join

The student table will be in the FROM clause so it will be a starting or LEFT table.

We’ll JOIN this to the student contact table or RIGHT table.

You’ll see that all of the students appear that are also in the contact table.

As shown in the tables below, studentID 9 is in the student table but NOT in the contact table so won’t appear in a join.

SQL Statement

SELECT a.studentID, a.FullName, a.programOfStudy,
b.`student-phone-cell`, b.`student-US-zipcode`
FROM student AS a
JOIN `student-contact-info` AS b ON a.studentID = b.studentID;

“Joined” data:

+-----------+------------------------+------------------+--------------------+--------------------+
| studentID | FullName               | programOfStudy   | student-phone-cell | student-US-zipcode |
+-----------+------------------------+------------------+--------------------+--------------------+
|         1 | Monique Davis          | Literature       | 555-555-5551       |              97111 |
|         2 | Teri Gutierrez         | Programming      | 555-555-5552       |              97112 |
|         3 | Spencer Pautier        | Programming      | 555-555-5553       |              97113 |
|         4 | Louis Ramsey           | Programming      | 555-555-5554       |              97114 |
|         5 | Alvin Greene           | Programming      | 555-555-5555       |              97115 |
|         6 | Sophie Freeman         | Programming      | 555-555-5556       |              97116 |
|         7 | Edgar Frank "Ted" Codd | Computer Science | 555-555-5557       |              97117 |
|         8 | Donald D. Chamberlin   | Computer Science | 555-555-5558       |              97118 |
+-----------+------------------------+------------------+--------------------+--------------------+

Left Join

Using the keyword LEFT before JOIN causes the system to start with the student (LEFT) table but will return NULL from the RIGHT table if there are no rows for the LEFT table student.

Note that studentID 9 appears here but the data from the contact table is just shown as NULL.

SELECT a.studentID, a.FullName, a.programOfStudy,
b.`student-phone-cell`, b.`student-US-zipcode`
FROM student AS a
LEFT JOIN `student-contact-info` AS b ON a.studentID = b.studentID;
+-----------+------------------------+------------------+--------------------+--------------------+
| studentID | FullName               | programOfStudy   | student-phone-cell | student-US-zipcode |
+-----------+------------------------+------------------+--------------------+--------------------+
|         1 | Monique Davis          | Literature       | 555-555-5551       |              97111 |
|         2 | Teri Gutierrez         | Programming      | 555-555-5552       |              97112 |
|         3 | Spencer Pautier        | Programming      | 555-555-5553       |              97113 |
|         4 | Louis Ramsey           | Programming      | 555-555-5554       |              97114 |
|         5 | Alvin Greene           | Programming      | 555-555-5555       |              97115 |
|         6 | Sophie Freeman         | Programming      | 555-555-5556       |              97116 |
|         7 | Edgar Frank "Ted" Codd | Computer Science | 555-555-5557       |              97117 |
|         8 | Donald D. Chamberlin   | Computer Science | 555-555-5558       |              97118 |
|         9 | Raymond F. Boyce       | Computer Science | NULL               |               NULL |
+-----------+------------------------+------------------+--------------------+--------------------+
9 rows in set (0.00 sec)

Complete table listings for reference

Student table listings

SELECT a.studentID, a.FullName, sat_score, a.programOfStudy, schoolEmailAdr 
FROM student AS a;

student or LEFT table

+-----------+------------------------+-----------+------------------+------------------------+
| studentID | FullName               | sat_score | programOfStudy   | schoolEmailAdr         |
+-----------+------------------------+-----------+------------------+------------------------+
|         1 | Monique Davis          |       400 | Literature       | [email protected] |
|         2 | Teri Gutierrez         |       800 | Programming      | [email protected]    |
|         3 | Spencer Pautier        |      1000 | Programming      | [email protected] |
|         4 | Louis Ramsey           |      1200 | Programming      | [email protected]   |
|         5 | Alvin Greene           |      1200 | Programming      | [email protected]   |
|         6 | Sophie Freeman         |      1200 | Programming      | [email protected]  |
|         7 | Edgar Frank "Ted" Codd |      2400 | Computer Science | [email protected]   |
|         8 | Donald D. Chamberlin   |      2400 | Computer Science | [email protected]  |
|         9 | Raymond F. Boyce       |      2400 | Computer Science | [email protected] |
+-----------+------------------------+-----------+------------------+------------------------+
9 rows in set (0.00 sec)
```sql
SELECT * from `student-contact-info` AS b;

student contact or RIGHT table

+-----------+----------------------------------+--------------------+--------------------+
| studentID | studentEmailAddr                 | student-phone-cell | student-US-zipcode |
+-----------+----------------------------------+--------------------+--------------------+
|         1 | [email protected]   | 555-555-5551       |              97111 |
|         2 | [email protected]  | 555-555-5552       |              97112 |
|         3 | [email protected] | 555-555-5553       |              97113 |
|         4 | [email protected]    | 555-555-5554       |              97114 |
|         5 | [email protected]     | 555-555-5555       |              97115 |
|         6 | [email protected]  | 555-555-5556       |              97116 |
|         7 | [email protected]    | 555-555-5557       |              97117 |
|         8 | [email protected]   | 555-555-5558       |              97118 |
+-----------+----------------------------------+--------------------+--------------------+
8 rows in set (0.00 sec)

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

I hope this at least gives you enough to get started.

Please see the manual for your database manager and have fun trying different options yourself.

Example of use

For this guide we’ll discuss the SQL RIGHT JOIN.

Right Join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table(table1) . The result is NULL from the left side, when there is no match.

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Complete table listings for reference

food or LEFT table data

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+



company or RIGHT table data
``` text
+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

To get company name from company table and company ID, item name columns from foods table, the following SQL statement can be used:

SELECT company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM   company
RIGHT JOIN foods
ON company.company_id = foods.company_id;

OUTPUT

COMPANY_ID COMPANY_NAME              COMPANY_CITY              COMPANY_ID ITEM_NAME
---------- ------------------------- ------------------------- ---------- --------------
18         Order All                 Boston                    18         Jaffa Cakes
15         Jack Hill Ltd             London                    15         Pot Rice
15         Jack Hill Ltd             London                    15         BN Biscuit
15         Jack Hill Ltd             London                    15         Cheez-It
16         Akas Foods                Delhi                     16         Chex Mix
17         Foodies.                  London                    17         Mighty Munch
NULL       NULL                      NULL                      NULL       Salt n Shake