SQLite Table Joining

Introduction

In this lab, you will learn how to combine data from multiple tables in SQLite using table joining techniques. We will cover INNER JOIN, LEFT JOIN, joining multiple tables, and filtering joined results. By the end of this lab, you'll be able to retrieve and combine data efficiently using SQLite.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/add_rows("Insert Multiple Rows") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") subgraph Lab Skills sqlite/init_db -.-> lab-552556{{"SQLite Table Joining"}} sqlite/make_table -.-> lab-552556{{"SQLite Table Joining"}} sqlite/add_rows -.-> lab-552556{{"SQLite Table Joining"}} sqlite/get_all -.-> lab-552556{{"SQLite Table Joining"}} sqlite/query_where -.-> lab-552556{{"SQLite Table Joining"}} end

Create and Populate Tables

In this step, you will create two tables, employees and departments, and populate them with sample data. These tables will be used in the subsequent steps to demonstrate table joining techniques.

First, open the SQLite shell by running the following command in the terminal:

sqlite3 /home/labex/project/company.db

This command opens the SQLite shell and connects to a database file named company.db. If the file doesn't exist, SQLite will create it.

Now, create the employees table with the following SQL command:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

This command creates a table named employees with three columns: id, name, and department_id. The id column is the primary key and will uniquely identify each employee.

Next, create the departments table with the following SQL command:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

This command creates a table named departments with two columns: id and name. The id column is the primary key and will uniquely identify each department.

Now, insert some sample data into the employees table:

INSERT INTO employees (name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', NULL);

This command inserts five rows into the employees table. Each row represents an employee and their corresponding department ID.

Finally, insert sample data into the departments table:

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

This command inserts three rows into the departments table. Each row represents a department and its name.

To verify that the tables have been created and populated correctly, you can run the following SQL command:

SELECT * FROM employees;

Expected Output:

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

And:

SELECT * FROM departments;

Expected Output:

1|Sales
2|Marketing
3|Engineering

Perform INNER JOIN Queries

In this step, you will learn how to use the INNER JOIN clause in SQLite. An INNER JOIN returns rows when there is a match in both tables based on a specified condition.

The basic syntax for INNER JOIN is:

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

In our case, we want to retrieve the employee names along with their department names. To do this, run the following SQL command:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This command joins the employees and departments tables based on the department_id column in employees matching the id column in departments. It then selects the employee's name from the employees table and the department's name from the departments table.

Expected Output:

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

This output shows the employee's name and their corresponding department name. Notice that Eve is not included in the result because her department_id is NULL, and there is no matching department. INNER JOIN only returns matching rows.

Use LEFT JOIN for Optional Data

In this step, you will learn how to use LEFT JOIN in SQLite. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, and the matching rows from the right table. If there is no match in the right table, NULL values are returned for the columns of the right table. This is useful when you want to retrieve all records from one table and include related data from another table if it exists.

The basic syntax for LEFT JOIN is:

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

To retrieve all employees and their department names, even if an employee doesn't have a department assigned, run the following SQL command:

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

This command joins the employees and departments tables based on the department_id column in employees matching the id column in departments. It selects the employee's name from the employees table and the department's name from the departments table. Because it's a LEFT JOIN, all employees will be listed.

Expected Output:

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering
Eve|

Notice that Eve is listed, but the department name is empty (NULL) because her department_id is NULL, and there's no matching department. This demonstrates how LEFT JOIN includes all rows from the left table (employees) even when there's no match in the right table (departments).

Join Multiple Tables

In this step, you will learn how to join multiple tables in SQLite. Joining more than two tables involves combining data from several related tables using multiple JOIN clauses.

First, let's add a new table called locations to store department locations. Run the following SQL command to create the locations table:

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

This command creates a table named locations with three columns: id, department_id, and city.

Next, insert some sample data into the locations table:

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

Now, let's join all three tables to retrieve employee names, department names, and department locations. Run the following SQL command:

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

This query first joins employees and departments based on employees.department_id = departments.id. Then, it joins the result with the locations table based on departments.id = locations.department_id. This links employees to their departments and then to the location of those departments.

Expected Output:

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

This shows each employee's name, their department's name, and the city where their department is located. Eve is not included because she doesn't have a department assigned.

Filter Joined Results

In this step, you will learn how to filter the results of joined tables using the WHERE clause in SQLite. Filtering allows you to retrieve only the rows that meet specific criteria after the join operation.

To retrieve the names of employees who work in the Sales department, run the following SQL command:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

This query joins the employees and departments tables and then filters the results to include only employees where the department name is 'Sales'.

Expected Output:

Alice
Charlie

This shows that Alice and Charlie work in the Sales department.

Now, let's retrieve the names of employees who work in departments located in New York. Run the following SQL command:

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

This query joins all three tables and then filters the results to include only employees where the department's location is New York.

Expected Output:

Alice
Charlie

Again, Alice and Charlie are the only employees in departments located in New York.

Summary

In this lab, you have learned how to combine data from multiple tables in SQLite using table joining techniques. You covered INNER JOIN, which returns rows when there is a match in both tables, and LEFT JOIN, which returns all rows from the left table and the matching rows from the right table. You also learned how to join multiple tables and filter joined results using the WHERE clause. These skills will enable you to retrieve and combine data efficiently using SQLite.