How to use SQL JOIN commands

SQL JOIN commands are among the most important SQL commands for analysing and processing relational databases. They make it possible to combine data from several tables. Depending on the specifics of your case, you can use INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN or FULL JOIN.

What are the SQL JOIN commands?

SQL is the standard programming and database language for working with databases, whether onsite or in the cloud. It makes it possible to create relational databases and perform data queries using various SQL commands. SQL JOIN commands are some of most important and most frequently used commands.

SQL JOINs can be used to query, link and combine data from different tables. The only prerequisite is that there is a logical connection between the tables. The connection is established with primary and foreign keys, which can be used to merge data using JOIN commands.

What are the different SQL JOIN commands?

There are several different SQL JOIN commands you can use, depending on the situation. Most of them use the same or similar parameters. These parameters are:

• SELECT: Defines columns that can be used with SQL JOIN. You can also select the entire data set with an asterisk (*).
• FROM: Defines the table that data will be taken from to merge with another table. -JOIN command: Creates a target table that, depending on the command used, joins, merges or compares the data in question.
• ON: Defines which JOIN condition is used for retrieving data.

The basic syntax for most SQL JOIN commands looks as follows:

SELECT  columnA,  columnB, …
FROM  tableA
JOIN-command  tableB
ON  tableA.column  =  tableB.column

Below we’ll introduce the most important SQL JOIN commands.

SQL INNER JOIN

This command can be used to merge data from two tables into one result table if there are two matching values in a shared column. SQL INNER JOIN is the most frequently used JOIN command and is the default if you don’t define a JOIN type.

Example:

INNER JOIN is used for two tables, ‘Orders’ and ‘Customers’. ‘Orders’ contains the columns ‘OrderNumber’, ‘CustomerID’, ‘ProductID’ and ‘OrderDate’. ‘Customers’ contains the columns ‘CustomerID’, ‘Name’ and ‘Address’. The CustomerID column is the primary key in the Customers table and is a foreign key in Orders. INNER JOIN uses the customer ID to combine all the orders and customer data into a result table.

The SQL INNER JOIN command looks as follows:

SELECT  orders.ordernumber, customers.name, orders.productid, orders.orderdate
FROM  orders
INNER JOIN  customers
ON  orders.customerid  =  customers.customerid

SQL OUTER JOIN

OUTER JOIN is the generic term for LEFT JOIN, RIGHT JOIN and FULL JOIN. Unlike INNER JOIN, OUTER JOIN also includes non-matching rows with a NULL value. We’ll take a closer look at LEFT JOIN, RIGHT JOIN and FULL JOIN.

SQL LEFT JOIN

The SQL LEFT JOIN command combines all the data from the left table with the matching data from the right table. Data without a match are assigned a NULL value.

Example:

SQL LEFT JOIN combines both matching and non-matching data from a left table called ‘Customers’ with a right table called ‘Vehicles’ using the shared column ‘VehicleID’. Unlike with INNER JOIN, the command also returns non-matching data with a NULL value.

The command looks as follows:

SELECT  *
FROM  customers
INNER JOIN  vehicles
ON  customers.vehicleid  =  vehicles.vehicleid

SQL RIGHT JOIN

SQL RIGHT JOIN combines data from a right table with matching data from a left table. For non-matching data, a NULL value is assigned.

Example:

Let’s take up our Orders and Customers tables from above. SQL RIGHT JOIN adds all the selected data from the left table Orders to the right table Customers using the customer ID. For non-matching rows, the command assigns the value NULL. That might be the case If there isn’t an order or a name for a customer ID.

The command looks as follows:

SELECT  orders.ordernumber, customers.name, orders.productid, orders.orderdate
FROM  customers
RIGHT JOIN  orders
ON  orders.customerid  =  customers.customerid

SQL FULL JOIN

SQL FULL JOIN combines LEFT JOIN and RIGHT JOIN and is also known as FULL OUTER JOIN. The command merges all the records and columns from both tables. NULL values are used for non-matching data.

Example:

FULL JOIN combines all matching and non-matching data from a right table called ‘Employees’ and a left table called ‘Department’, using the ‘DepartmentID’. If an employee hasn’t been assigned a department, there will be a NULL value under Department.

The SQL FULL JOIN command looks as follows:

SELECT  *
FROM  employees
FULL JOIN  department
ON  employees.departmentid  =  department.departmentid

SQL CROSS JOIN

SQL CROSS JOIN is also known as Cartesian JOIN. It joins all the records from the left and right table independent of matches. Since it returns all possible combinations, it doesn’t need to use ON.

Example:

CROSS JOIN merges all the data from the right table (Products) with the left table (Orders).

The command looks as follows:

SELECT  *
FROM  products
CROSS JOIN  orders

SQL NATURAL JOIN

NATURAL JOIN combines all the records from two tables using shared columns. The ON parameter isn’t used, as a shared column isn’t a prerequisite and the command automatically joins all the shared columns.

Example:

NATURAL JOIN combines the Team table and the Department table using their shared columns. The command will only return results if there is at least one shared column.

The command looks like this:

SELECT  *
FROM  team
NATURAL JOIN  department

SQL SELF JOIN

SQL SELF JOIN combines a table with itself by using it as both the left and right table. A SELF JOIN operation can be performed using any of the above commands. In the syntax, t1 and t2 serve as aliases for the right and left table, since they are the same table.

Basic syntax, using LEFT JOIN:

SELECT  *
FROM  tableA AS t1
LEFT JOIN  tableA AS t2
ON  t1.columnname  =  t2.columnname