For an instructor lead, in-depth look at learning SQL click below.
w can I extract data from multiple tables using SQL?
Extracting information from multiple tables in SQL is a common operation and often involves the use of joins. Join statements enable retrieving information from several tables that have a column in common. In this blog, we’ll guide you through the basics of using JOINs in SQL and show examples of SQL code to better illustrate these concepts.
Understanding SQL JOIN
A SQL JOIN is used to combine rows from two or more tables, based on a related column between them. The four types of joins in SQL are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. However, the most commonly used type of join is the INNER JOIN.
Example of an INNER JOIN
|
1 2 3 4 5 6 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; |
In the code above, we are retrieving the customer’s name and order id by connecting two tables – “Customers” and “Orders”. The connection happens through the common column “CustomerID”.
LEFT JOIN and RIGHT JOIN
While the INNER JOIN only shows the records where there was a match in both tables, a LEFT JOIN shows all the records from the left table and matched records from the right table. If there is no match, the result is NULL on the right side. A RIGHT JOIN is the opposite of a LEFT JOIN. It will return all the records from the right table and the matched records from the left table.
Example of a LEFT JOIN
|
1 2 3 4 5 6 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; |
In this code, we are fetching all customers and their respective orders. If a customer doesn’t have any order, we still get the Customer information but the OrderID would be NULL.
FULL JOIN
A FULL JOIN will potentially return a lot of data because it shows all records when there is a match in either the left table or the right table.
Example of a FULL JOIN
|
1 2 3 4 5 6 |
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID; |
The example above would display all customers and all orders. If a customer doesn’t have any order, we still get the CustomerName but the OrderID would be NULL, and vice versa.
In conclusion, extracting data from multiple tables in SQL does not need to be complex. It is simply a matter of using the appropriate JOIN statement to merge the tables based on the related column. As you got familiar with each type of JOIN and their use, you can now enhance the power of your SQL code!