Joining Tables

Joining tables in SQL refers to the process of fetching related data from two or more tables based on common columns. This operation allows for a comprehensive view of information spread across different tables, facilitating more complex queries and reports. Databases often split data into multiple tables to avoid redundancy and save space. Joins reunite this split data when needed.

In essence, joins are pivotal in relational databases to leverage the relationships between tables and extract meaningful, combined data sets.

Table Example

Let's imagine we had two tables: users and jobs.

• Users Table: Will have columns for a unique user_id, username, and job_id (which will be a foreign key linking to the jobs table).
• Jobs Table: Will have columns for a unique job_id and job_name.

In the examples below, the letters PK means primary key, and FK means foreign key.

Users Table

Jobs Table

In this setup:

• user_id is the primary key for the users table.
• job_id is the primary key for the jobs table.
• In the users table, job_id is also a foreign key that references the job_id in the jobs table.

Performing a Join

Let's say we wanted to grab a user and their job as one result. We could use a join. The keyword for performing a join in SQL is JOIN. The most commonly used type of join is the INNER JOIN.

To use it, you'll specify the tables you want to join and the condition upon which they should be joined. Here's an example based on the users and jobs tables:

SELECT users.username, jobs.job_name
FROM users
INNER JOIN jobs ON users.job_id = jobs.job_id;

Here's a breakdown:

1. SELECT users.username, jobs.job_name: This part of the query specifies that we want to retrieve the username from the users table and the job_name from the jobs table.
2. FROM users: This initiates the query from the users table.
3. INNER JOIN jobs: This instructs the database to join the users table with the jobs table.
4. ON users.job_id = jobs.job_id: This is the condition for the join. It states that the job_id column in the users table should match the job_id column in the jobs table.

When executed, this query would return a list of user names alongside their respective job names.

Aliases

You may not be a fan of referring to each table by its name in your queries. For readability, you may want to consider using aliases.

Aliases in SQL are temporary names given to tables or columns for the duration of a query. They can make queries more readable, especially when dealing with joins involving tables with longer names or when joining tables with shared column names.

• Aliases can shorten table or column names making the SQL statements more readable.
• When two tables have columns with the same name, aliases help avoid confusion by providing a unique reference for each column.
• Typing shorter aliases can speed up the process of writing queries, especially with tables that have long or complex names.

An alias can be created by adding the AS keyword after the table name followed by the alias name. Let's use aliases for our users and jobs table join:

SELECT u.username, j.job_name
FROM users AS u
INNER JOIN jobs AS j ON u.job_id = j.job_id;

• users AS u: We're giving the users table the alias u.
• jobs AS j: We're giving the jobs table the alias j.
• In the SELECT and ON clauses, we use these aliases (u and j) instead of the full table names.

By using aliases, the query becomes shorter and more streamlined, and if there were columns with the same name in both tables, the aliases would clarify which column we're referring to.

Different Types of Joins

SQL joins are used to combine rows from two or more tables based on related columns. There are several types of joins, each serving a different purpose. Let's break down the common ones:

• INNER JOIN:
  • Use: Fetches rows from both tables that meet the given condition.
  • Scenario: If you wanted to see only the users who have a job, you'd use an INNER JOIN. Any user without a job wouldn't appear in the results.
• LEFT JOIN:
  • Use: Fetches all rows from the left table and matching rows from the right table. If there is no match, the result is NULL for the right table's columns.
  • Scenario: If you wanted a list of all users and their jobs but still wanted to see users even if they don't have a job, you'd use a LEFT JOIN.
• RIGHT JOIN :
  • Use: The opposite of LEFT JOIN. It fetches all rows from the right table and matches rows from the left. If there is no match, the result is NULL for the left table's columns.
  • Scenario: Suppose you had a list of jobs and wanted to see who was employed in each role but still wanted to list jobs even if no one had them.

Why Use One Over the Other?

• If you want only matched records from both tables, use an INNER JOIN.
• If you want all records from one table and the matched records from the second table (and don't mind having NULL values), use a LEFT JOIN or RIGHT JOIN.

Remember, the type of join you choose impacts the results you get, so it's crucial to pick the one that fits your data retrieval needs.

Key Takeaways

• Joins combine rows from two or more tables based on related columns.
• Remember, while working with joins, it's vital to ensure that you're joining on columns that are related, usually through primary and foreign key relationships.
• Aliases can be used to give temporary names to tables or columns in joins to make queries more readable and to avoid ambiguities, especially when tables have columns with the same names.
• INNER JOIN fetches rows that meet a condition from both tables. Only returns rows where there's a match in both tables.
• LEFT JOIN fetches all rows from the left table and matching rows from the right table. Returns all rows from the left table, even if there's no match in the right table.
• RIGHT JOIN is the opposite of LEFT JOIN. Fetches all rows from the right table and matching rows from the left table.