SQL Select From Multiple Tables With Examples

One of the most common tasks that database developers and administrators need to constantly perform is writing SQL queries. There are a variety of situations in which you might need to pull data from multiple tables, such as when analysing customer behaviours or preparing detailed reports about sales figures – but it can be tricky for newcomers to visualize how these pieces fit together. In this blog post, we’ll provide you examples for SQL select from multiple tables using JOIN clause.

Understanding SQL Joins

The JOIN clause is an essential part of SQL queries because it enables a program to combine more than one table into one result set. There are a few different kinds of joins – inner joins, outer joins, left and right outer joins. By selecting specific criteria from different databases, programs can use the JOIN query to craft powerful and extensive search results that bring together multiple potential points of data for analysis. Below are some examples of using the SELECT statement with the JOIN clause to retrieve data from multiple tables in a SQL database

INNER JOIN

An INNER JOIN in SQL is used to combine rows from two or more tables based on a matching column in each table. It returns only those rows that satisfy the join clause.

INNER JOIN example from two tables

SELECT * FROM customers INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This SELECT statement retrieves all the rows from the customers and orders tables where the customer_id columns match. The resulting single table will contain all the columns from both the customers and orders tables.

Select from multiple tables with INNER JOIN clause

Here is an example of an INNER JOIN with three tables:

SELECT * FROM table1 t1
INNER JOIN table2 t2 ON t1.common_column = t2.common_column
INNER JOIN table3 t3 ON t2.common_column = t3.common_column
WHERE t1.column = 'some value';

In this example, with the join operation we are selecting all columns (*) from table1 (t1), table2 (t2), and table3 (t3). We are then using INNER JOIN to combine the rows from the three tables based on a common column in each table. The common column is specified using the ON clause in the INNER JOIN statements. Finally, we are using the WHERE clause to filter the results to only those rows where the value in the column of table1 is ‘some value’.

This INNER JOIN will return all rows from the three tables that satisfy the join clause (i.e., the common column has the same value in each table) and the WHERE clause condition (i.e., the value in the column of table1 is ‘some value’).

LEFT JOIN

This type of join also known as left outer join returns all rows from the left table (the first table in the FROM clause), and any matching rows from the right table (the second table in the FROM clause). If there is no match, NULL values are returned for right table’s columns.

LEFT JOIN example with two tables

SELECT * FROM customers 
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

This SELECT statement retrieves all rows from the customers table, and any matching rows from the orders table based on the customer_id column. If a customer does not have any orders, the orders columns for that customer will contain NULL values.

Select from multiple tables with LEFT JOIN clause:

SELECT c.customer_id, c.name, o.order_id, o.total_amount, p.product_id, p.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id

In this example, we are selecting data from the customers table (c), the orders table (o), and the products table (p). The customers and orders tables are joined on the customer_id column, and the orders and products tables are joined on the order_id column.

The result of this query will be a table containing all customers and their corresponding orders and products. If a customer does not have any orders, the order_id and total_amount columns will be NULL. If an order does not have any products, the product_id and name columns will be NULL.

Note that the order of the tables in the LEFT JOIN clause is important. In this example, the customers table is the left table, and the orders and products tables are the right tables. This means that all rows from the customers table will be included in the result, even if there is no match in the right tables. If we had reversed the order and put the orders table on the left and the customers table on the right, the result would only include orders that had a matching customer.

RIGHT JOIN

A RIGHT JOIN, also known as a RIGHT OUTER JOIN, returns all rows from the right-hand table specified in the ON condition and any matching rows from the left-hand table. If there is no match, NULL values are returned for left-hand columns.

RIGHT JOIN example with two tables

SELECT * FROM customers RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This SELECT statement retrieves all rows from the orders table, and any matching rows from the customers table based on the customer_id column. If an order does not have a corresponding customer, the customers columns for that order will contain NULL values.

Select from multiple tables with RIGHT JOIN clause

SELECT orders.order_id, customers.customer_name, products.product_name FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
RIGHT JOIN products ON orders.product_id = products.product_id;

This query will return all rows from the products table, as well as any matching rows from the customers and orders tables. If there is no match in the orders table for a particular row in the products table, NULL values will be returned for the order_id and customer_name columns.

The result of this query would be a table containing all product names, along with the corresponding customer name and order id for each product (if available).

FULL OUTER JOIN

A FULL OUTER JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN. It returns all rows from both tables, filling in NULL values for missing matches on either side.

FULL OUTER JOIN example with two tables

SELECT * FROM customers FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

This SELECT statement retrieves all rows from both the customers and orders tables, regardless of whether there is a match based on the customer_id column. If a customer does not have any orders, the orders columns for that customer will contain NULL values. If an order does not have a corresponding customer, the customers columns for that order will contain NULL values.

Select from multiple tables with of a FULL OUTER JOIN clause

SELECT * FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id
FULL OUTER JOIN products p ON o.product_id = p.product_id

In this example, the FULL OUTER JOIN combines the customers and orders tables using the customer_id column as the join key. It also combines the resulting table with the products table using the product_id column as the join key.

The result of this query will be a table containing all rows from the customers and orders tables, as well as any matching rows from the products table. Any rows that do not have a match in one of the tables will have NULL values for the missing columns.

For example, if a customer has placed an order for a product, the resulting row will have the customer’s information, the order details, and the product information. If a customer has not placed an order, the resulting row will have the customer’s information and NULL values for the order and product columns. Similarly, if an order was placed for a product that is no longer in the products table, the resulting row will have the order and customer information, as well as NULL values for the product columns.

Cross Join

A cross join, also known as a cartesian join, is a type of join in SQL that produces the cartesian product of two or more tables. This means that it returns all possible combinations of rows from each table, regardless of whether there is a match in the other table(s).

CROSS JOIN example with two tables

SELECT * FROM table1
CROSS JOIN table2;

This query will return all possible combinations of rows from table1 and table2. If table1 has 3 rows and table2 has 4 rows, the result of the cross join will have 12 rows (3 * 4). Each row in the result will contain all the columns from both table1 and table2.

Cross joins can be useful in certain situations, such as when you want to create a list of all possible combinations of two or more sets of data. However, they can also be dangerous if used improperly, as they can result in very large result sets that could potentially strain the database’s resources. It is important to use caution when using cross joins, and to only use them when they are truly necessary.