Explicit Inner Join Vs Implicit Inner Join

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

SELECT columns
FROM table1, table2
WHERE table1.column_name=table2.column_name;

💡 In the join clause, it is not necessary that we always use the ‘=' sign. It can be >=, <=, < or !=. We will discuss more on this in the coming blogs.

Analysis of Explicit and Implicit Inner Joins

Let’s jump into the analysis of the Explicit and Implicit inner joins with the help of some practical examples.

Firstly we need a sample database, for this, we are going to use Microsoft’s sample database named Northwind.

This database contains the sales data for a fictional store called Northwind Traders, which imports and exports specialty foods from around the world. You can get the sample database from here.

💡 The SQL code mentioned on this entire blog is written with PostgreSQL dialect in mind.

Assuming that you have already dumped the Northwind Database to your PostgreSQL’s local database, If not don’t worry the below code shows how to dump the data to your existing empty database

$: psql -d your_database_name -f northwind_database.sql

Let’s list out the tables in the database. We can use the below query to get a more formatted output.

SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY table_name;

💡 Alternatively, you can also use the command \dt to list the databases,

You should get an output similar to this.

Performing INNER JOIN with three tables

Let’s consider the following tables (categories, suppliers, products).

Table categories

Table suppliers

Table products ( Displaying only first 10 records )

Let’s consider a sample exercise and generate the output with both Explicit Inner Join and Implicit Inner Join and analyze.

Sample Exercise:-

We need to write a SQL query to display the id, name, category name, and supplier name of all the products that belong to the categories Beverages and Sea Food.

Performing Explicit Inner Join

Here to achieve the above scenario, we need to join the three tables categories, products, and suppliers with two INNER JOINs.

The Query

SELECT p.product_id, p.product_name, c.category_name, s.company_name 
FROM products AS p 
INNER JOIN categories AS c 
ON p.category_id=c.category_id 
INNER JOIN suppliers AS s 
ON p.supplier_id=s.supplier_id 
WHERE c.category_name in ('Beverages', 'Seafood')
ORDER BY p.product_id;

Here, the above SQL query does two specific actions listed below:

• joins the products table and categories table based on category_id
• and joins the products table and suppliers table based on supplier_id

The command returns only those records where there is a match between column values in both the JOIN conditions and also the conditions given in the WHERE clause.

The Output

Performing Implicit Inner Join

Here to achieve the above scenario, we need to list the three tables and add the join and filter conditions inside the WHERE clause.

The Query

SELECT p.product_id, p.product_name, c.category_name, s.company_name 
FROM products p, categories c, suppliers s 
WHERE p.category_id=c.category_id 
AND p.supplier_id=s.supplier_id 
AND c.category_name in ('Beverages', 'Seafood')
ORDER BY p.product_id;

Here, the above SQL query does the following:

• All the JOIN conditions and the conditions to filter products based on categories are given in the WHERE clause, where each condition is separated by an AND operator.

The Output is the same data that we saw in the explicit query output.

Query Analysis

We have replaced the following explicit query segment

FROM products AS p 
INNER JOIN categories AS c 
ON p.category_id=c.category_id 
INNER JOIN suppliers AS s 
ON p.supplier_id=s.supplier_id
WHERE c.category_name in ('Beverages', 'Seafood')

with the implicit query segment as given below.

FROM products p, categories c, suppliers s 
WHERE p.category_id=c.category_id 
AND p.supplier_id=s.supplier_id 
AND c.category_name in ('Beverages', 'Seafood')

Conclusion

From the above analysis of the sample exercise, we were able to see the different ways that we could query the data using the two types of Inner joins.

We were also able to understand that, the explicit join queries are easier to understand and less prone to errors, they are easy to debug but their query size will be larger as compared to implicit join queries. They are more suitable for situations where we use complex SQL queries with very less frequent access and for those people who are not much familiar with the database tables and their relations.

On the other hand, the implicit join queries are difficult to understand and more prone to errors, they are difficult to debug but their query size will be smaller as compared to explicit join queries. They are more suitable for those who frequently access databases having a clear idea about the table relations and also for those who use SQL queries in their daily work. These people can write complex and concise implicit join queries very easily.

Thanks for reading!