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.
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.ComputerIdThis 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:
Loading...
Episode 1 - Start with SELECT FROM
Episode 2 - Renaming a Column with an Alias
