Episode 3: Joining Two Tables Using SQL, Two Different Ways

Joining two tables using SQL makes it possible to combine data from two (or more) tables based on a similar column between the tables. The JOIN clause will affiliate rows that have matching values in both tables from the column being joined on and render the results across rows in the result set. 

Joining Two Tables using SQL

There are different ways to join tables that will render different result sets, but this example features an ‘INNER’ join.

Tip: The ‘JOIN’ clause will always default to an ‘INNER JOIN’, even without including the word ‘INNER’. Using ‘INNER’ is optional.

When using SQLite, it is possible to join tables in more than one way. To see a live demonstration, check out Episode 3 of SQL Snippets from Scratch here.

Method 1: Join with the ON clause

SELECT *
FROM Computers
JOIN ComputerScans ON Computers.ComputerId = ComputerScans.ComputerId

This method is more of a standard practice when joining tables together because it is the primary method used in other forms of SQL. 

Tip: An advantage of using the ON clause is that you can join tables together even when the column names may not match in the tables being joined. There are times this will occur in SQL although it is unlikely it will be used in a video example in this series.

Method 2: Join with the USING clause

SELECT *
FROM Computers
JOIN ComputerScans USING (ComputerId)

The USING clause may be used when the columns being joined share the exact same name. Otherwise, a syntax error will occur.

Warning: In PDQ Inventory, ‘SELECT *’ throws a syntax error when joining the Computers and ComputerScans table because there is more than one column in each table with matching names.  Therefore, only specific columns are referenced in the query and executed in the result set.

Wrapping up

Watch this video for more hands-on help and don’t forget to check out our previous SQL blogs too:

Episode 1 – Start with SELECT FROM

Episode 2 – Renaming a Column with an Alias

 

Your email address will not be published.

Your Name

This site uses Akismet to reduce spam. Learn how your comment data is processed.