How to Combine Records Using SQL - SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

SQL Bootcamp: Learn the Basics of SQL Programming in 2 Weeks (2016)

Chapter 8. How to Combine Records Using SQL

You may use the JOIN clause to combine records from multiple databases. Basically, JOIN is a method that can combine fields from different tables.

The Different Types of JOIN

The SQL computer language supports different kinds of JOIN. These are:

· SELF JOIN– You can use this if you want to link a table to itself as if you are working on different tables. While doing this, you should rename at least one table in your SQL command.

· RIGHT JOIN– This JOIN retrieves all data rows from the right table. SQL will complete this task even if no matches exist between the two tables.

· LEFT JOIN– This JOIN returns all data rows from the left table. The SQL language will complete this even if no matches exist between the tables involved.

· FULL JOIN– This JOIN retrieves data rows if one of the tables has a match.

· INNER JOIN– This returns data rows if both tables have a match.

· CARTESIAN JOIN– This JOIN retrieves the Cartesian values of the record sets from the joined tables.

Let’s discuss each JOIN in detail:

The INNER JOIN

This is one of the most important joins in SQL. It generates a new table by mixing the column values of different tables. The database query checks the rows of all tables to determine if there are row pairs that meet the join-predicate’s requirements. If the pairs of rows satisfy the join-predicate, the values for those rows are placed in a new table. Here’s the syntax that you should use:

The LEFT JOIN

This JOIN retrieves all data rows from the left table. SQL does this even if the right table doesn’t have any match. Thus, if your command’s ON clause has 0 matches with the right table, you’ll still get a data row from the process. However, the columns from the right table will have NULL values inside them.

The syntax of this join is:

The RIGHT JOIN

This JOIN returns all data rows from the table on the right. The SQL language will do this even if the left table doesn’t have any match. Basically, you’ll still get at least one data row from this process even if your command’s ON clause has no matches with the left table. However, the columns from the left table will contain NULL values.

The syntax of this join is:

The FULL JOIN

This SQL JOIN mixes the results of the left and right joins. The new table will have all of the records from the two tables. Here’s the syntax that you should use:

Important Note: FULL JOIN uses NULL values to fill records that don’t match.

The SELF JOIN

You should use this join if you want to link a table to itself. As noted earlier, you have to rename at least one of the tables in your SQL statement.

The syntax of this JOIN is:


The CARTESIAN JOIN

This JOIN retrieves the Cartesian products of the record sets from the tables that you are using. Because of this, SQL users consider this as an INNER JOIN whose join-condition is always true. The syntax of the CARTESIAN JOIN is: