There are times when you need to fetch related data that reside in the same table. For that, a special kind of join is required called a self join. In today's blog, we'll learn how to write a query that includes a self join using Navicat Premium as the database client.
Syntax
The basic syntax of SELF JOIN is as follows:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
In addition to the linking on common fields, the WHERE clause could contain other expressions based on your specific requirements.
An Example
In the Sakila Sample Database, there is a customer table that contains customer-related information such as their name, email, and address. Here is are the columns in the Navicat Table Desginer:
We can use a self join to retrieve all customers whose last name matches the first name of another customer. We achieve this by assigning aliases to the customer table. The aliases allow us to join the table to itself because they give the table two unique names, which means that we can query the table as though it were two different tables. These are then joined on the last_name and first_name fields:
SELECT
c1.customer_id as customer_1_id,
c1.first_name as customer_1_first_name,
c1.last_name as customer_1_last_name,
c2.customer_id as customer_2_id,
c2.first_name as customer_2_first_name,
c2.last_name
FROM customer c1,
customer c2
WHERE c1.last_name = c2.first_name
ORDER BY c1.last_name;
Navicat's auto-complete feature is really useful when writing your queries because it helps avoid typos and having to guess at column names. For this reason, it's especially useful for selecting fields:
Executing the query generates the following results:
Using an INNER JOIN
Another way to link a table to itself is to use an INNER JOIN. If you're not sure how to do that, Navicat can help! It provides a useful tool called Query Builder for building queries visually. It allows you to create and edit queries without much knowledge of SQL. The database objects are displayed in left pane. Whereas in the right pane, it is divided into two portions: the upper Diagram Design pane, and the lower Syntax pane.
We can simply drag the last_name field of the first table alias to the first_name of the second table alias and the Query Builder will generate the JOIN for us!
Here's the generated SQL statement:
SELECT
c1.customer_id AS customer_1_id,
c1.first_name AS customer_1_first_name,
c1.last_name AS customer_1_last_name,
c2.customer_id AS customer_2_id,
c2.first_name AS customer_2_first_name,
c2.last_name
FROM
customer AS c1
INNER JOIN customer AS c2 ON c1.last_name = c2.first_name
ORDER BY
customer_1_last_name ASC
;
Conclusion
In today's blog, we learned how to write a query that includes a self join using Navicat Premium. Give Navicat Premium a try. You can evaluate it for 14 days completely free of charge!
