How To Create Multiple Tables In One Database In Sqlite

SQL join multiple tables is one of the most popular types of statements executed while handling relational databases. As known, there are five types of join operations: Inner, Left, Right, Full and Cross joins.

In this article, we will explain the meaning of Joins in SQL, we will describe each one of the Join operation types and we will show the main use cases where it is used by providing examples.

Why using Joins?

Joins are used to combine the rows from multiple tables using mutual columns. As an example, assume that you have two tables within a database; the first table stores the employee's information while the second stores the department's information, and you need to list the employees with the information of the department where they are working. In that case, you must find a way to SQL Join multiple tables to generate one result set that contains information from these tables. Noting that joins can be applied over more than two tables.

To apply join between two tables, one table must contain a column that is a reference for the other table. In the example above, the Employees table must have a column that contain a reference key for the department (ex: Department id ).

As mentioned above, there are multiple approaches to SQL join multiple tables. We will describe each approach briefly in the next sections.

Creating tables used in examples

To illustrate different types of joins, we will create Employees and Departments tables as following:

Employees table:
- Employee_id (int) / identifier
- Employee_name (varchar)
- Employee_DOB (date)
- Department_Id (reference to departments table)
Departments table:
- Department_id (int) / identifier
- Department_Name (varchar)

There are four departments defined within the Departments table:

Human resources
Development
Sales
Technical Support

And there are five employees defined within the Employees table:

Alan Smith (Department: Human Resources)
Sultan Nader (Department: Human Resources)
Mohd Rasheed (Department: Development)
Brian Wallace (Department: Sales)
Peter Hilton (Not assigned to a department until now)

We have used the following commands to create this example:

-- Create employees table

CREATE TABLE #Employees ( Employee_id int , Employee_name varchar ( 250 ) , Employee_DOB date , Department_ID int )

-- Create departments table

CREATE TABLE #Departments ( Department_id int , Department_Name varchar ( 250 ) )

-- Insert values into departments table

INSERT INTO #Departments ( Department_id , Department_Name )

VALUES ( 1 , 'Human Resources' ) , ( 2 , 'Development' ) , ( 3 , 'Sales' ) , ( 4 , 'Technical Support' )

-- Insert values into employees table

INSERT INTO #Employees ( Employee_id , Employee_name , Employee_DOB , Department_ID )

VALUES ( 1 , 'Alan Smith' , '19890101' , 1 ) ,

( 2 , 'Sultan Nader' , '19920101' , 1 ) ,

( 3 , 'Mohd Rasheed' , '19990101' , 2 ) ,

( 4 , 'Brian Wallace' , '19790101' , 3 ) ,

( 5 , 'Peter Hilton' , '19860101' , NULL )

INNER JOIN

Inner join is the most popular join type, it selects the rows where values are the mutual columns values are matched. If we apply an inner join to the Employees-Departments example, it will only return the employees working within departments that have a row within the Departments table:

INNER JOIN illustration

Back to the Employees and Departments tables, to select the employees that are working within departments we can use the following query:

SELECT Employee_id , Employee_name , Employee_DOB , Department_Name

FROM #Departments INNER JOIN #Employees

ON #Departments . Department_id = #Employees . Department_ID

As shown in the query above, first we need to specify the columns we want to retrieve within the SELECT clause, then we need to specify the tables we need to read from and to specify the join type within the FROM clause, also we need to specify the columns used to perform the join operation after the ON keyword. The result of the query mentioned is as shown in the following screenshot:

Using INNER approach SQL join multiple tables

From the screenshot above, we can see that the fifth employee is not shown in the result since it is not assigned to any department. Also, we can note that the Department_Name is retrieved instead of the Department id .

LEFT JOIN

Another SQL join multiple tables approach, is LEFT JOIN which is used to retrieve all rows from the first table mentioned in the FROM clause in addition to the matched rows from the second table:

LEFT JOIN illustration

Back to the Employees and Departments tables, if we need to select all employees listed within the Employees table and to mention the department name if exists, we can use the following query:

SELECT Employee_id , Employee_name , Employee_DOB , Department_Name

FROM #Employees LEFT JOIN #Departments

ON #Departments . Department_id = #Employees . Department_ID

The query result is as shown in the following screenshot:

Using LEFT approach SQL Join multiple tables

As we can see, the query result returned all five employees listed within the table with a NULL value in the Department _ Name column in the fifth row since Peter Hilton is not assigned to any department yet.

RIGHT JOIN

The next SQL join multiple tables approach is RIGHT JOIN, which is very similar to LEFT JOIN since it returns all rows from the table listed at the right of the JOIN operator with the matched values from the table listed at the left:

RIGHT JOIN illustration

Back to the Employees and Departments tables, if we need to select the employees that are working within departments, in addition to departments that does not have any employees, we can use the following query:

SELECT Employee_id , Employee_name , Employee_DOB , Department_Name

FROM #Employees RIGHT JOIN #Departments

ON #Departments . Department_id = #Employees . Department_ID

As shown in the screenshot below, the query returned the same rows of the INNER JOIN query in addition to the Technical support department that doesn't have any employee:

Using RIGHT approach to SQL Join multiple tables

FULL OUTTER JOIN

FULL OUTTER JOIN is another approach used to SQL join multiple tables. It returns all matched rows between both tables specified in the JOIN operation in addition to all unmatched rows from the first and second tables:

FULL JOIN illustration

Back to the Employees and Departments tables, the Full join query will return all employees working within departments plus all employees that are not assigned and all departments that doesn't contain any employee:

SELECT Employee_id , Employee_name , Employee_DOB , Department_Name

FROM #Employees FULL JOIN #Departments #Departments . Department_id = #Employees . Department_ID

From the screenshot below, we can see that Peter Hilton is with no value in the Department_Name field, and Technical support department is shown with no employee information:

Using FULL approach to SQL Join multiple tables

CROSS JOIN

The last approach used to SQL Join multiple tables is CROSS join which is a bit different from the other Join operations. It is used to create a combination of two different sets without have mutual columns. As an example, if we need to create a combination of all departments with all employees.

Example:

SELECT Employee_id , Employee_name , Employee_DOB , Department_Name

FROM #Employees CROSS JOIN #Departments

Result:

Using CROSS approach to SQL Join multiple tables

Conclusion

Joining table is one of the main uses of SQL language. In this article, we have explained why using Joins, and we illustrated five different approaches to SQL Join multiple tables by providing some examples. We noted that Inner, Left, Right, and Full joins require mutual columns between tables while Cross join is to multiply to rows of the first table with the ones stored in the second table.

Author
Recent Posts

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a Neo4j and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah