There are mainly two approaches to joining the tables, either using the names of multiple tables with the WHERE clause in the SELECT statement or using a JOIN statement. Which approach do you use often?
In this tutorial, we will see both approaches with some examples and see why joins are preferred over the list of multiple tables in the FROM statement with the where clause. So, let’s get started!
Introduction
We can easily join the tables using the multiple table names in the FROM statement with the where clause. However, SQL provides us with different types of JOIN statements to join the table to get the preferred result.
There are a bunch of advantages of using the JOIN statement over the other method of using multiple table names. SQL supports both methods and you are free to use any. Let’s see both of them in depth first.
Using Multiple Table Names in FROM
This is the method where we simply write the multiple table names and join them using the where clause. Let’s see an example to understand how it is done.
First, we will need some tables.
Here, we have two tables – students and student_marks.
Now, we will write a query to display the student names and their marks without using the join, i.e., we will list both table names in the FROM clause and use the where clause on the common attribute.
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s, student_marks m
WHERE s.roll_number=m.roll_number;Code language: SQL (Structured Query Language) (sql)Here, the roll_number attribute is common in both tables. Therefore, we have used it to join the table using the WHERE clause.
Let’s check the result.
As you can see, the result is correct, which means the query we wrote above is working perfectly. However, it’s not the best way to join the tables.
Let’s now write a query to join the tables using the JOIN keyword.
Join Tables Using the JOIN Keyword
The JOIN keyword makes the work easier by allowing the developers to write queries to join the tables which are readable and easy to understand for others. Also, there are multiple types of JOINs that are provided by SQL for ease of writing the queries.
Now, let’s write a query using the JOIN statement to get the exact same result as the above.
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s
JOIN student_marks m
ON s.roll_number=m.roll_number;Code language: SQL (Structured Query Language) (sql)Here, we have used the simple JOIN statement to join two tables. Note that, there are also other types of joins available such as left join, right join, inner join etc. Anyway, let’s execute the query and see the result.
As you can see, we got the exact same result.
Now you might be thinking, if both methods produce the exact same result, then which one to use and why?
Let’s discuss it.
JOIN vs. Multiple Tables in FROM – Which is better?
Note that, there is no performance difference between these two methods.
These are only two different ways to get a similar result having different syntax but both are the same thing.
However, the new method (using the JOIN keyword) is much clear to read and understand.
Using the list of multiple table names is the old way of joining the table which is ANSI-89. Whereas, the ANSI-92 introduced a new method of joining the tables using the JOIN keyword. This clearly tells us the new method is better for a reason.
Following are the reasons why joining tables using the JOIN keyword is preferred over using the list of multiple table names.
Reason 1 – Easy to Read and Understand
If you notice, the old method consists of the where clause. This WHERE clause doesn’t help to filter the result. It is nothing but a way to specify the column names for the join operation. If you want to filter the data, you have to write another condition and merge it with the AND operator.
For example-
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s, student_marks m
WHERE s.roll_number=m.roll_number
AND m.maths >= 80;Code language: SQL (Structured Query Language) (sql)Here you can see, only the second condition here works as a filtering condition, therefore, it looks messy and becomes a little bit complicated to understand.
If we use the JOIN keyword to write the above query, it becomes easy to understand because the filtering conditions get separated.
Let’s have a look.
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s
JOIN student_marks m
ON s.roll_number=m.roll_number
WHERE m.maths >=80;Code language: SQL (Structured Query Language) (sql)As you can see, the where clause consists of only one condition which acts as a filtering condition.
Reason 2 – Easy to Join Multiple Tables
When you want to join multiple tables, it becomes super easy if you use the JOIN keyword instead of the old method.
We have written the tutorial on joining multiple tables already so that you can learn it easily.
Reason 3 – Easy to Join Using LEFT, RIGHT, FULL, INNER Joins
It is very easy to write the inner as well as outer joins using the INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN keywords.
If you try to write these queries using the old way, the query will definitely become messy and difficult to write.
Conclusion
In this tutorial, we have learned the two ways to join the tables, their differences and why is the new method better than the old one. I hope you have understood why joining the tables using the JOIN keyword is preferred over the old method of writing multiple table names. You can share this tutorial with your friends too to let them know about the cool stuff!