Joining tables using SQL

To obtain data that is stored across many tables, SQL includes functions that can be used to join tables. Data is frequently stored in numerous tables in databases, and these tables can be connected to one another. For instance, a database might contain tables for customers and orders, each of which is linked to a particular client. All the orders connected to a certain customer or all the customers who have placed orders can be found by joining these two tables.

Types of Joins in SQL

Inner Join: An inner join only displays the rows with identical values in both joined tables. If you link a database of customers with a table of orders, for instance, the only rows that appear in the result are those where the client has placed an order.

SELECT orders.order_id, customers.customer_name, orders.order_date

FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;

This joins the orders table and customers table on the customer_id column, and selects the order_id, customer_name, and order_date columns. Only the rows that have matching values in both tables are included in the result set.

Left Join: All the rows from the left table and the matching rows from the right table are returned by a left join. The result will have NULL values for the right table's columns if there isn't a matching row in the right table. If you left join, for instance, a table of customers and a table of orders, the output will include all of the customers and their orders, even if they haven't yet placed any.

SELECT customers.customer_name, orders.order_date

FROM customers

LEFT JOIN orders ON customers.customer_id = orders.customer_id;

This chooses the customer_name and order_date fields when joining the customers table and orders table using the customer_id column. The result set contains all rows from the customers table as well as any matching rows from the orders table. The result set will have null values for the order_date column if there are no matching entries in the orders table.

Right Join: An entire row from the right table and any matching rows from the left table are returned by a right join. The result will have NULL values for the left table's columns if there is no matching record in the left table. For instance, if you right-join a table of customers and a table of orders, the result will include all of the orders and all of the customers—even those who haven't placed any orders—who placed them.

SELECT customers.customer_name, orders.order_date

FROM customers

RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

This is comparable to the left join example, but it also includes any matching rows from the customers table as well as every row from the orders table. The result set will include null values for the customer_name column if there are no matching entries in the customers table.

Full Outer Join: When doing a full outer join, all the rows from both tables are returned, and the columns for which there are no matching rows in the other table are filled with NULL values. For instance, a full outer join between a table of customers and a table of orders will produce a result that includes all of the customers and all of their orders, even if they haven't placed any, as well as all of the orders and the customers who put them, even if they didn't have any customers.

SELECT customers.customer_name, orders.order_date

FROM customers

FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

This chooses the customer_name and order_date fields when joining the customers table and orders table using the customer_id column. The result set contains all rows from both tables as well as null values for any columns that don't match.