How to JOIN SELECT SQL

If you need to retrieve data from multiple tables in a database, you can use a JOIN statement in your SELECT statement. In this tutorial, you will learn how to use the JOIN statement to combine data from two or more tables in a SQL database.

Types of Joins

There are four types of joins in SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Each join type returns a different set of results, based on the relationship between the tables being joined.

INNER JOIN

The INNER JOIN statement returns only the rows that have matching values in both tables being joined. Here is an example:

SELECT customers.customer_id, orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

This statement returns a list of all customer IDs and order IDs where there is a match between the two tables.

LEFT JOIN

The LEFT JOIN statement returns all rows from the left table and only the matching rows from the right table. If there is no match in the right table, the result will be NULL. Here is an example:

SELECT customers.customer_id, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This statement returns a list of all customer IDs and order IDs from the customers table, with matching order IDs from the orders table. If there is no match, the order ID will be NULL.

RIGHT JOIN

The RIGHT JOIN statement returns all rows from the right table and only the matching rows from the left table. If there is no match in the left table, the result will be NULL. Here is an example:

SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

This statement returns a list of all customer IDs and order IDs from the orders table, with matching customer IDs from the customers table. If there is no match, the customer ID will be NULL.

FULL OUTER JOIN

The FULL OUTER JOIN statement returns all rows from both tables, with NULL values where there is no match in either table. Here is an example:

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

This statement returns a list of all customer IDs and order IDs from both tables, with NULL values where there is no match in either table.

Conclusion

Using the JOIN statement in your SELECT statement allows you to combine data from multiple tables in your SQL database. Understanding the different types of joins available to you will help you retrieve the data you need in a more efficient way.