**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Working with Multiple Tables: Joins and Relationships **Topic:** Write queries using different types of joins to retrieve related data from multiple tables.(Lab topic) Welcome to this hands-on lab where you'll learn how to write queries using different types of joins to retrieve related data from multiple tables in SQLite. By the end of this topic, you'll be able to write efficient and accurate queries that combine data from multiple tables. **Types of Joins in SQLite** In the previous topic, we introduced you to the basics of JOIN operations in SQLite. In this lab, we'll dive deeper into the different types of joins and provide hands-on examples. 1. **INNER JOIN** An INNER JOIN returns only the rows that have matching values in both tables. Example: ```sql CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total REAL NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); INSERT INTO customers (customer_id, name) VALUES (1, 'John Doe'), (2, 'Jane Doe'); INSERT INTO orders (order_id, customer_id, total) VALUES (1, 1, 100.00), (2, 1, 200.00), (3, 2, 50.00); SELECT customers.name, orders.total FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; ``` This query returns the name of each customer and the total amount they've spent, but only for customers who have placed at least one order. 2. **LEFT JOIN** A LEFT JOIN returns all rows from the left table, and matching rows from the right table. If there's no match, the result will contain NULL values. Example: ```sql CREATE TABLE suppliers ( supplier_id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE products ( product_id INTEGER PRIMARY KEY, supplier_id INTEGER NOT NULL, name TEXT NOT NULL, price REAL NOT NULL, FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id) ); INSERT INTO suppliers (supplier_id, name) VALUES (1, 'ABC Inc.'), (2, 'XYZ Corp.'), (3, ' DEF Inc.'); INSERT INTO products (product_id, supplier_id, name, price) VALUES (1, 1, 'Product A', 10.99), (2, 1, 'Product B', 5.99), (3, 2, 'Product C', 7.99); SELECT suppliers.name, products.name AS product_name, products.price FROM suppliers LEFT JOIN products ON suppliers.supplier_id = products.supplier_id; ``` This query returns the name of each supplier, the name of each product they supply, and the price of each product. If a supplier doesn't supply any products, the result will contain NULL values. 3. **RIGHT JOIN** A RIGHT JOIN is similar to a LEFT JOIN, but returns all rows from the right table, and matching rows from the left table. Example: ```sql CREATE TABLE employees ( employee_id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE departments ( department_id INTEGER PRIMARY KEY, department_name TEXT NOT NULL, manager_id INTEGER, FOREIGN KEY (manager_id) REFERENCES employees (employee_id) ); INSERT INTO employees (employee_id, name) VALUES (1, 'John Smith'), (2, 'Jane Doe'), (3, 'Bob Brown'); INSERT INTO departments (department_id, department_name, manager_id) VALUES (1, 'Sales', 1), (2, 'Marketing', 2), (3, 'HR'); SELECT departments.department_name, employees.name AS manager FROM departments RIGHT JOIN employees ON departments.manager_id = employees.employee_id; ``` This query returns the name of each department, and the name of the manager responsible for each department. If a department doesn't have a manager, the result will contain NULL values. 4. **FULL JOIN** A FULL JOIN returns all rows from both tables, with NULL values in the columns where there are no matches. Example: ```sql CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, total REAL NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); INSERT INTO customers (customer_id, name) VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'Bob Brown'); INSERT INTO orders (order_id, customer_id, total) VALUES (1, 1, 100.00), (2, 1, 200.00), (3, 2, 50.00); SELECT customers.name, orders.total FROM customers FULL JOIN orders ON customers.customer_id = orders.customer_id; ``` This query returns the name of each customer, and the total amount they've spent. If a customer hasn't placed an order, the result will contain NULL values. **Tips and Best Practices** * Use the `USING` clause when joining tables on multiple columns. * Use the `ON` clause when joining tables on a single column. * Use the `ORDER BY` clause to sort the results of your join operation. * Use the `LIMIT` clause to limit the number of rows returned by your join operation. * Avoid using the `SELECT \*` clause, as it can return unnecessary columns and slow down your query. **Conclusion** In this lab, you learned how to write queries using different types of joins to retrieve related data from multiple tables in SQLite. You also learned some tips and best practices for optimizing your join operations. **What's Next** In the next topic, we'll cover inserting new data into tables using the `INSERT INTO` statement. You'll learn how to insert single rows, multiple rows, and data from another table. **External Resources** * [SQLite Documentation: SELECT](https://www.sqlite.org/lang_select.html) * [W3Schools: SQL Joins](https://www.w3schools.com/sql/sql_join.asp) * [GeeksforGeeks: SQL Joins](https://www.geeksforgeeks.org/sql-joins/) **Need Help or Have Questions?** If you need help or have questions about this topic, please leave a comment below.