WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Joins



SQL join is used to join rows from two or more tables.

The following figure shows 7 usages related to LEFT JOIN, RIGHT JOIN, INNER JOIN, OUTER JOIN.


SQL JOIN

SQL JOIN clause is used to combine rows from two or more tables, based on common fields between those tables.

The most common type of JOIN: SQL INNER JOIN (simple JOIN) . SQL INNER JOIN returns all rows that satisfy the JOIN condition from multiple tables.

Demo database

In this tutorial, we will use the welookups sample database.

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.com/ | 1     | USA     |
| 2  | Indiatimes          | https://www.Indiatimes.com/| 13    | IN                  |
| 3  | zomato      | http://www.zomato.com/ | 4689  | IN                  |
| 4  | indiamart          | http://indiamart.com/      | 20    | IN                  |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Here is the data for the "access_log" site visit log:

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

Note that the " id " column in the "Websites" table points to the field " site_id " in the "access_log" table. The two tables above are linked by the "site_id" column.

Then if we run the following SQL statement (including INNER JOIN):

Example

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

The result of executing the above SQL is as follows:

h2> Different SQL JOIN

Before we continue to explain the examples, let's list the different SQL JOIN types you can use:

  • INNER JOIN : If there is at least one match in the table, return rows
  • LEFT JOIN : return all rows from the left table even if there is no match in the right table
  • RIGHT JOIN : return all rows from the right table even if there is no match in the left table
  • FULL JOIN : As long as there is a match in one of the tables, the rows are returned