realLifeinfo

What is SQL join clause and how to use it

Nerd, Solutions 

SQL join is the mechanism of combining (joining) data from more than one table based on a common attribute - foreign key - which is used to join them.

Sponsored

The ability to combine tables on common attributes is one of the most important feature of a relational database system.

In SQL, join operations are performed by using the JOIN clause most of the time, or can be performed by listing table names in the FROM clause.

Syntax Example:

Using table list:

Three kinds of Join clause

There are three kinds of SQL join clauses, and each will produce a different result.

SQL natural join:

Will join tables by selecting only the rows with common values in their common attributes.

SQL equijoin:

This will join tables by selecting only rows that match the specified equality condition.

SQL Cross join:

A cross join will perform a relational product of the two tables. This join is also known as the Cartesian join.

All three join can be classified into two categories: inner join and outer join.

Sponsored

Inner Join: this will only return the rows that match the join condition.

Outer Join: this will return not only the matched rows, but unmatched rows will also be returned. The outer join can be divided into two subcategories: left outer join - will return unmatched rows from the left table, and right outer join - will return unmatched rows from the right table of the join clause.

Various SQL join clause syntax

SQL joining clause can be written in various syntaxes. Here are some of examples:

Join using equality symbol (=)

This can also be written like this:

The different between these two syntaxes is the use of WHERE in a second syntax while in the first syntax we use ON.

This is an inner join but can also be used in an outer join by replacing the join clause with the left or right join clause.

Join using natural join

This is a natural join syntax, and can be used to join more than two tables. Example:

Join using cross join

This is the syntax for a cross join.

Join using (using) clause

Sponsored

This is an inner join

This is an outer join.

This syntax will use the specified column name in the USING(emp_id) function to join both tables.

Join using (on) clause

This is an inner join, but also can be used in an outer join.

All these syntaxes can be used to join more than two tables.

Examples:

SQL join is not limited to only SELECT statement. It can also be used in UPDATE statement to update multiple tables at the same time using single SQL query, and for DELETE statements. Check your SQL database vendor for full documentation. 

Sponsored