In order to use a JOIN properly, we must have some understanding of the data and tables that we’re using. It’s important since we need to know the data relationships between the tables. If there’s any uncertainty around a table join, we should always ask someone with knowledge of the database to make sure we’re joining two tables correctly. All too often assumptions are made when ID or key columns are named the same between tables. Someone thinks they must related to each other and makes an invalid join. These assumptions can prove to be costly and provide additional headache so just be careful!
What does a table join look like?
How about joining multiple tables?
We can see the SQL syntax is the same when joining a new table. Let’s assume that we know the Customer table can join to the Invoice table. The Invoice table has a CustomerId field that can join to the Invoice table on CustomerId. We also know that we could join the InvoiceLine table by the InvoiceId.
It’s important to note that the table fields have consistent names that make it easier to identify joins. The fields are named the same between tables making them easier to identify. Let’s just not assume this is always the case as this can get us into trouble. Databases are not always designed the best so if we’re unsure about table joins we should see if someone can provide an ERD (entity-relationship diagram) which shows tables and the join relationships.
In our case, we don’t have to actually assume anything. We can generate the diagram to view the relationships in our SQLite database by highlighting all tables in the Database Navigator, right-clicking, and then select ‘Create New ER Diagram’. Save the diagram as a reference.
JOIN vs INNER JOIN – What’s The Difference?
It’s completely personal preference whether we decide to include the INNER keyword in a SQL JOIN. Although we hear it commonly referred to as an inner join the INNER part isn’t required when writing SQL. These commands mean the same thing. Using the JOIN operator is exactly the same as using INNER JOIN. Just remember, INNER is always optional. Some people have an easier time understanding the action when INNER is specified in the SQL query. Again, personal preference.
We could write the previous SQL example with the INNER keyword added for the exact same result.
Using Table Aliases To Get Exactly What We Want
Although we may have all the tables we need, we may not have the results we are expecting. Let’s assume we don’t want to select everything from the tables. Using the * value returns all columns in every table and that’s not what we want. To clean it up and return what we want we will want to use table aliases.
How do we write a table alias in SQL?
First – we want to clean up all the table names and joins. We can use the AS keyword after each table to declare an alias. For Customer, we will use ‘C’, Invoice – ‘I’, and InvoiceLine – ‘IL’. The AS keyword is completely optional, but easier to read and understand. Note that each type of database varies on it’s requirements for using the AS or not.
Much cleaner, but we still don’t want to return all columns from the table. Let’s assume we only want to see the customer names, invoice dates, totals, quantity, and price.
By using the alias_table.column_name syntax, we can return values in a specific table in our query. Aliases can also be used on the column names if we want to name them differently. Let’s say we want to shorten first and last name columns.
In the results returned, we can see the column names have changed. This can be useful for writing scripts that generate views or reports. They may need to use business naming conventions instead of using the default database field names since those don’t always align.