SQL CROSS JOIN Keyword

The CROSS JOIN keyword returns all records from both tables (table1 and table2).

CROSS JOIN Syntax

SELECT column_name(s)

FROM table1

CROSS JOIN table2;

Given below is a selection of name table 

followed by a selection  of age table.

SQL CROSS JOIN Example

The following SQL statement selects all customers, and all orders:

Note: The CROSS JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "name" that do not have matches in "age", or if there are rows in "age" that do not have matches in "name", those rows will be listed as well.

If you add a WHERE clause (if table1 and table2 has a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:

SQL Full Outer Join

Full outer join in SQL helps combine data from two tables. One can begin writing a query along the lines of select data from a table. The query is then followed by 'full outer join' keyword and the second table name whose data needs to be combined with the first table. Then there is an on clause which joins the data between the two tables based on common values between similar fields in the two tables.

In the below example we have a table named 'name'.

Our second table would be a table named 'age'.

The empID (employee ID) column is the common factor between the two tables. We will thus perform a join on these columns. The output should be a combination of the two tables. Hence the output must contain the columns 'empID', 'name' and 'age'. We will leverage the concept of full outer join as shown below to achieve our goal. Note that in a full outer join all records from the right hand table are returned along with all the records from the left hand side table.

SQL Right Join

Right join in SQL helps combine data from two tables. One can begin writing a query along the lines of select data from a table. The query is then followed by 'right join' keyword and the second table name whose data needs to be combined with the first table. Then there is an on clause which joins the data between the two tables based on common values between similar fields in the two tables.

In the below example we have a table named 'name'.

Our second table would be a table named 'age'.

The empID (employee ID) column is the common factor between the two tables. We will thus perform a join on these columns. The output should be a combination of the two tables. Hence the output must contain the columns 'empID', 'name' and 'age'. We will leverage the concept of right join as shown below to achieve our goal. Note that in a right join all records from the right hand table are returned while matching values are returned by the left hand side table.

SQL Left Join

Left join in SQL helps combine data from two tables. One can begin writing a query along the lines of select data from a table. The query is then followed by 'left join' keyword and the second table name whose data needs to be combined with the first table. Then there is an on clause which joins the data between the two tables based on common values between similar fields in the two tables.

In the below example we have a table named 'name'.

Our second table would be a table named 'age'.

The empID (employee ID) column is the common factor between the two tables. We will thus perform a join on these columns. The output should be a combination of the two tables. Hence the output must contain the columns 'empID', 'name' and 'age'. We will leverage the concept of left join as shown below to achieve our goal. Note that in a left join all records from the left hand table are returned while matching values are returned by the right hand side table.

SQL Inner Join

Inner join in SQL helps combine data from two tables. One can begin writing a query along the lines of select data from a table. The query is then followed by 'inner join' keyword and the second table name whose data needs to be combined with the first table. Then there is an on clause which joins the data between the two tables based on common values between similar fields in the two tables.

In the below example we have a table named 'name'.

Our second table would be a table named 'age'.

The empID (employee ID) column is the common factor between the two tables. We will thus perform a join on these columns. The output should be a combination of the two tables. Hence the output must contain the columns 'empID', 'name' and 'age'. We will leverage the concept of inner join as shown below to achieve our goal.