MySQL JOIN

SQL Joins clause is allows to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
Consider the following two tables, CUSTOMERS and ORDERS tables are as follows:

CUSTOMERS TABLE:

ID NAME AGE ADDRESS SALAY
1 Ramesh 32 Ahmedabad 20000
2 Kavita 25 Delhi 16000
3 Chandni 25 Mumbai 23000
4 Komal 36 MP 19000
5 Kaushik 23 Kota 33000
6 shailu 26 Chandigad 33000
7 Piyush 24 Haridwar 33000

ORDERS TABLE :

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 30000
100 2009-10-8 00:00:00 9 15000
101 2009-11-20 00:00:00 2 15000
103 2008-05-20 00:00:00 4 20000
SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

ID NAME AGE AMOUNT
3 Chandni 25 30000
4 Komal 36 20000

NOTE: Join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be used to join tables. However, the most common operator is the equal symbol.

SQL Join Types:

There are different types of joins available : They are following:

  • INNER JOIN
  • OUTER JOIN
  • SELF JOIN
  • CARTESIAN JOIN

INNER JOIN :

The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate.
The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate.When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax: The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2… FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;

Ex: Let us see an example of INNER JOIN is as follows:

SELECT ID,NAME,AMOUNT,DATE FROM CUSTOMERS INNER JOIN ORDERS CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

OUTER JOIN:

The Outer join can be classified into 3 types. They are:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Left Outer Join:

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax: The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1,table2.column2 … FROM table1 LEFT JOIN table2 ON table1.common_filed = table2.common_field;

Ex: Let us see an example of Left JOIN is as follows:

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

RIGHT JOIN:

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

Also, this means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
Syntax: The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1,table2.column2…FROM table1 RIGHT JOIN table2 ON table1.common_filed = table2.common_field;

Ex: Let us see an example of RIGHT JOIN is as follows:

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

FULL JOIN:

The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Syntax: The basic syntax of FULL JOIN is as follows:

SELECT table1.column1, table2.column2… FROM table1 FULL JOIN table2 ON table1.common_filed = table2.common_field

Ex: Let us see an example of FULL JOIN is as follows

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS FULL JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

SELF JOIN:

The SQL SELF JOIN is used to join a table to it as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax: The basic syntax of SELF JOIN is as follows:

SELECT a.column_name, b.column_name…FROM table1 a, table1 b WHERE a.common_filed = b.common_field;

Ex: Let us see an example of SELF JOIN is as follows:

SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a,CUSTOMERS b WHERE a.SALARY < b.SALARY;

CARTESIAN JOIN:

The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records from the two or more joined tables.
Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.
Syntax: The basic syntax of CROSS JOIN is as follows:

SELECT table1.column1, table2.column2… FROM table1, table2 [table3];

Ex: Let us see an example of CROSS JOIN is as follows:

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS, ORDERS;