code-with-kemi

Working With Multiple Tables In Sql

Rukayat Balogun's photo
Rukayat Balogun
·

4 min read

Table of contents

A lot of times, a database administrator would require to fetch data from two tables where they have something in common. In this case, you will have to work on joining these tables where they share similar information. In SQL, we call this joins as this allows us to join multiple tables where there is a common column(s). Here is a list of joins we have

  • Inner join

  • Full join

  • Self join

  • Outer join

    • Left join

    • Right join

Inner join

The SQL Standard defines several ways to perform a JOIN, the most common of which is the INNER JOIN. An INNER JOIN between the Students table and the Classes table returns rows in the Students table linked with the related rows in the Classes table but it returns neither students who have not yet registered for any classes nor any classes for which no student is registered. An INNER JOIN returns only those rows where the linking values match in both of the tables or result sets.

Syntax

SELECT * FROM customer
INNER JOIN
orderDetails
ON
customer.id = orderDetails.customer_id;

SELECT * FROM  orderDetails o
INNER JOIN
customer c
ON
c.id = o.customer_id;

Outer join

An OUTER JOIN asks your database system to return not only the rows that match the criteria you specify but also the unmatched rows from either one or both of the two sets you want to link. You’ll generally use the OUTER JOIN form that asks for all the rows from one table or result set and any matching rows from a second table or result set. To do this, you specify either a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

When you begin building queries using OUTER JOIN, the SQL Standard considers the first table you name as the one on the “left,” and the second table as the one on the “right.” So, if you want all the rows from the first table and any matching rows from the second table, you’ll use a LEFT OUTER JOIN. Conversely, if you want all the rows from the second table and any matching rows from the first table, you’ll specify a RIGHT OUTER JOIN.

Syntax

SELECT * FROM  customer
RIGHT OUTER JOIN
orderDetails
ON
customer.id = orderDetails.customer_id;

The above block of code will return all the records on the customer table and corresponding records on the orderDetails table.

SELECT * FROM  customer
LEFT OUTER JOIN
orderDetails
ON
customer.id = orderDetails.customer_id;

The above block of code will return all the records on the orderDetails table and corresponding records on the customer table.

Optionally, you can use the keyword USING of the matching column are named the same way.

Syntax

SELECT * FROM  customer
LEFT OUTER JOIN
orderDetails
USING
id;

The above code will work if both tables have id column with the same data type.

Full Outer Join

A FULL OUTER JOIN is neither left nor right—it’s both! It includes all the rows from both of the tables or result sets participating in the JOIN. When no matching rows exist for rows on the left side of the JOIN, you see Null values from the result set on the right. Conversely, when no matching rows exist for rows on the right side of the JOIN, you see Null values from the result set on the left.

Syntax

SELECT * FROM  customer
FULL JOIN
orderDetails
ON
customer.id = orderDetails.customer_id;

Union Join

No discussion of OUTER JOINs would be complete without at least an honor able mention to UNION JOIN. In the SQL Standard, a UNION JOIN is a FULL OUTER JOIN with the matching rows removed. A UNION lets you select the rows from two or more similar result sets and combine them into a single result set.

Syntax

SELECT FirstName FROM customer
UNION
SELECT FirstName FROM customer

Union All

When you include the ALL keyword, no duplicate rows are removed. UNION ALL is likely to be much more efficient because your database system doesn’t have to do extra work to look for and eliminate any duplicate rows. If you’re certain that the queries you are combining with UNION don’t contain any duplicate rows (or you don’t care about duplicates), then always use the ALL keyword.

SELECT FirstName FROM customer
UNION ALL
SELECT FirstName FROM customer
joinsDatabasesSQL