What are Explicit Joins?
An explicit join refers to the use of the JOIN keyword to explicitly define the relationship between tables in a query. In explicit joins the tables to be joined are listed in the FROM Clause and the relationship between them is specified using the JOIN keyword along with the ON keyword to define the join condition.
Consider we have the following three tables called EMPLOYEES, DEPARTMENT, and EMPLOYEE_DEPARTMENT. Here EMPLOYEES consists of id, first_name, last_name, and DEPARTMENT consists of id, Nname and EMPLOYEE_DEPARTMENT consist of employee_id, department_id as Columns.
Database(Explicit Join)
Example 1
Let’s Retrieve the first name and last name of employees along with the name of their departments.
SELECT employees.first_name, employees.last_name, departments.name
FROM employees
INNER JOIN employee_department ON employees.id = employee_department.employee_id
INNER JOIN departments ON employee_department.department_id = departments.id;
Output:
Explicit join (Example-1 output)
Explanation: This query uses two inner joins to retrieve data from three tables which are employees, employee_department and departments. It selects the first name and last name of employees along with the name of their departments. The INNER JOIN keyword is used to return only the rows where there is at least one match in both tables.
Example 2
Let’s Retrieve the first name and last name of employees along with the name of their departments, including employees who are not currently assigned to any department.
SELECT employees.first_name, employees.last_name, departments.name
FROM employees
LEFT JOIN employee_department ON employees.id = employee_department.employee_id
LEFT JOIN departments ON employee_department.department_id = departments.id;
Output:
Explicit join (Example-2 output)
Explanation: In the above query we uses two left joins to retrieve data from three tables which are “employees,” “employee_department,” and “departments.” It selects the first name and last name of employees along with the name of their departments. The LEFT JOIN keyword is used to ensure that all records from the “employees” table are included in the result even if there is no matching record in the “employee_department” or “departments” tables.
Example 3
Let’s Retrieve the first name and last name of all employees along with the name of all departments, creating a combination of every employee with every department, regardless of their actual assignments.
SELECT employees.first_name, employees.last_name, departments.name
FROM employees
CROSS JOIN departments;
Output:
Example-3 output
Explanation: In the above Query, We performs a cross join between the “employees” and “departments” tables, combining every row from the “employees” table with every row from the “departments” table. This results in a Cartesian product where each row in the output represents a combination of an employee’s first name and last name with a department’s name.
Explicit vs Implicit Joins in SQLite
When working with SQLite databases, users often need to retrieve data from multiple tables. Joins are used to combine data from these tables based on a common column. SQLite supports two types of joins which are explicit joins and implicit joins. In this article, We’ll learn about Explicit vs implicit joins in SQLite along with their differences and some examples and so on.