SQL JOIN with Examples

SQL JOIN

• A SQL JOIN combines records from two tables.
• A JOIN locates related column values in the two tables.
• A query can contain zero, one, or multiple JOIN operations.
• INNER JOIN is the same as JOIN; the keyword INNER is optional.

Different types of JOINs

• (INNER) JOIN: Select records that have matching values in both tables.
• LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
• RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
• FULL (OUTER) JOIN: Selects all records that match either left or right table records.

   

The SQL JOIN syntax

The general syntax is:

1. SELECT column-names FROM table-name1 JOIN table-name2 ON column-name1 = column-name2 WHERE condition

The general syntax with INNER is:

1. SELECT column-names FROM table-name1 INNER JOIN table-name2 ON column-name1 = column-name2 WHERE condition

SQL JOIN Examples

Table1

mysql> select *from customers; select *from orders;
+------------+---------------+-------------+---------+
| customerid | customername  | contactname | country |
+------------+---------------+-------------+---------+
| 1          | Subham Ball   | joy         | india   |
| 2          | Mike Wooksahi | Mike        | maxico  |
| 3          | jon Wooksahi  | Jon         | maxico  |
+------------+---------------+-------------+---------+
3 rows in set (0.00 sec)

Table2

+---------+------------+------------+
| orderid | customerid | orderdate  |
+---------+------------+------------+
| 10308   | 2          | 1996-09-18 |
| 10309   | 37         | 1996-09-19 |
| 10310   | 77         | 1996-09-20 |
+---------+------------+------------+
3 rows in set (0.00 sec)

 1) Example of join 

mysql> select orders.orderid, customers.customername, orders.orderdate from orders inner join customers on orders.customerid = customers.customerid;
+---------+---------------+------------+
| orderid | customername  | orderdate  |
+---------+---------------+------------+
| 10308   | Mike Wooksahi | 1996-09-18 |
+---------+---------------+------------+
1 row in set (0.00 sec)

2) Example of join with order by Clause

mysql> select orders.orderid, customers.customername, orders.orderdate from orders inner join customers on orders.customerid = customers.customerid order by customers.customername;
+---------+---------------+------------+
| orderid | customername  | orderdate  |
+---------+---------------+------------+
| 10308   | Mike Wooksahi | 1996-09-18 |
+---------+---------------+------------+
1 row in set (0.00 sec)