Two ways to join tables in SQL – using JOIN Clause vs Where Clause

Joins are an integral part of working with data, especially in SQL. When data is stored in tables, joins are used to connect the data points as necessary for retrieving full information. There are two different clauses that can be used to join tables. 

1) JOIN Clause

JOIN Clause is the most commonly used method to join data from different tables. There are four types of join functions available depending on the nature of the join required. These are INNER JOIN, OUTER JOIN, LEFT JOIN AND RIGHT JOIN. To learn more about joins, visit this link here: https://www.w3schools.com/sql/sql_join.asp 

INNER JOIN is the most common and widely used join in SQL. Most of the time, the clause is just called JOIN and it defaults to an INNER JOIN. 

Let’s use the CHINOOK database to test an example. 

Example using JOIN Clause

select * from tracks

join genres on tracks.genreid = genres.genreid

join albums on tracks.AlbumId = albums.AlbumId;

The query above joins the tracks table with the genres table using GenreID and joins the tracks table again with the albums table using the AlbumID. 

Let’s answer a question that required joining data from different tables. 

Question: List all Latin songs

Answer: select * from tracks t join genres g on t.genreid = g.genreid and g.Name = “Latin”;

Note: Here t and g are used as aliases for the table names. 

 

2) WHERE Clause

Now, the WHERE clause is usually used to add filters or conditions, however, you can also use this to join tables! Interesting, huh! In fact, sometimes the WHERE clause can be much easier to read and quicker to write. 

Let’s redo the example above using the WHERE clause. 

Note: The WHERE clause would only work in place of an INNER JOIN. For LEFT, RIGHT and OUTER JOINs, you’d need to use the specific JOIN clauses.

Example redone using the WHERE Clause

select * from tracks, genres, albums

where tracks.GenreId = genres.GenreId

and tracks.AlbumId = albums.AlbumId;

The query above joins the tracks table with genres and albums tables. It connects the tracks table to genres table using GenreID and albums table using the AlbumID.

Let’s answer the same question from earlier in this example using the WHERE clause. 

Question: List all Latin songs

Answer: select * from tracks, genres where tracks.GenreId = genres.GenreId and genres.Name = “Latin”;

Want to learn more of these tips and tricks, especially with an experienced industry-leading instructor in a live session? Check out the beginner-friendly Data Analytics Courses at LDA (Learn Data Analytics) – Beginner-Friendly Data Analytics Courses, Hands-on Learning, Live & Online Classrooms, Led by Industry Experts! 

1800-400-5321 | info@learndataanalytics.ca | www.learndataanalytics.ca