SQL Join (Inner, Left, Right and Full Joins) – Details Updated

SQL Join (Inner, Left, Right and Full Joins)

SQL joins are used to fetch or retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in SQL join. Data tables are related to each other with keys. We use these keys relationships in SQL to join. A primary key is a column or a combination of columns with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.

A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. The 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.

Check Truncate Vs. Delete In SQL

Types Of Joins: There are different types of join, they are

• Self Join
• Inner Join
• Outer Join
• Cross Join

Inner Join

The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.

Check Also: SQL Interview Questions

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. The INNER JOIN in SQL joins two tables according to the matching of certain criteria using a comparison operator.

Syntax:

Select * FROM table1 INNER JOIN table2 ON 
table1.column_name = table2.column_name;

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: the Key column of the participating tables.

Example:

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

Self Join [SQL Join]

A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY, which references its PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.

The self-join can be viewed as a join of two copies of the same table. The table is not copied, but SQL performs the command as though it were.

Check Also: SQL query interview questions

The syntax of the command for joining a table to itself is almost the same as that for joining two different tables. To distinguish the column names from one another, aliases for the actual the table name are used, since both the tables have the same name. Table name aliases are defined in the FROM clause of the SELECT statement.

Syntax:

SELECT a.column_name, b.column_name…
FROM table1 a, table1 b
WHERE a.common_

filed = b.common_field;
Here WHERE clause could be any given expression based on your requirement.

Example:

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

Outer Join [ SQL Join ]

The SQL OUTER JOIN clause is a variation of the SQL JOIN clause that enables a SELECT statement to access more than one table. The JOIN clause controls how tables are linked. It is a qualifier of the SQL FROM clause.

Read Also: SQL Queries For Testers

The SQL OUTER JOIN returns all rows from both the participating tables, which satisfy the join condition along with rows that do not satisfy the join condition. The SQL OUTER JOIN operator (+) is used only on one side of the join condition.

The subtypes of SQL OUTER JOIN are,

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

Syntax:

Select * FROM table1, table2 WHERE conditions [+];

Parameters:

table1, table2: Name of the tables participating in joining.
Conditions: Conditions.

Left Join [ SQL 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 if the ON clause matches 0 (zero) records in the right table, the join will still return a row in the result, but with NULL in each column from 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 the LEFT JOIN is

Select * FROM table1 LEFT OUTER JOIN table2 
ON table1.column_name=table2.column_name;

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:

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

Right, Join [ SQL Join ]

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table, the join will still return a row in the result. But with NULL in each column from the left table. 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 a RIGHT JOIN is

Select * FROM table1<br> RIGHT OUTER JOIN table2 
ON table1.column_name=table2.column_name;

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:

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

Full Join

In SQL, the FULL OUTER JOIN combines the results of both the left and right outer joins and returns all matched or unmatched rows from the tables on both sides of the join clause.

Syntax:

Select * FROM table1 FULL OUTER JOIN table2 
ON table1.column_name=table2.column_name;

Parameters:

table1, table2: Name of the tables participating in joining.
column_name: Column of the participating tables.

Example:

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

Cross Join

The SQL CROSS JOIN produces a result set, which is the number of rows in the first table multiplied by the number of rows in the second table if nowhere clause is used along with CROSS JOIN. This kind of result is called a Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

Synta]x:

Select * FROM table1 CROSS JOIN table2;

Parameters:

table1, table2: Name of the tables participating in joining.

Example:

SQL> SELECT ID, NAME, AMOUNT, DATE 
FROM CUSTOMERS, ORDERS

If you find any Topic is not covered in the list, please comment in this blog post so that I can update it in this list.