How do I join two tables with common column in SQL?

Based on your edit, you need an INNER JOIN based on the combination of id and type in both tables:

Inhaltsverzeichnis Show

Why using Joins?
Creating tables used in examples
FULL OUTTER JOIN

SELECT Table1.id , Table1.type , Table1.ColumnsYouNeed , Table2.ColumnsYouNeed FROM Table1 INNER JOIN Table2 ON Table1.id = Table2.id AND Table1.type = Table2.type

This would ensure that you only see those rows from both tables where the ID and Types match.

Example: If Table1 contains id = 12345 and type = 'foo', and Table2 has id = 12345 and type = 'foo', you will see the data (based on the respective columns you add to the SELECT statement) from both Table1 and Table2.

If Table1 contains id = 12345 and type = 'foo', but Table2 has id = 12345 and only type = 'bar', you will not see either rows.

Should you want to see all values in Table1, regardless of whether they are match in Table2, change INNER JOIN to LEFT JOIN. Should you want to show all values from Table2, regardless of whether or not they exist in Table1, you could change INNER JOIN to RIGHT JOIN.

* UPDATE based on your comment *

I figured I'd show some additional examples of how JOINs work.

Let's start by setting up some sample tables with sample data:

CREATE TABLE #table1 (id tinyint, myType tinyint, columnA varchar(5)); INSERT INTO #table1 VALUES (1, 1, 'aaaa'), (1, 2, 'aaab'), (1, 4, 'aaaac'), (2, 1, 'bbbbb'), (2,2, 'bbbbc'), (3, 1, 'dddd'); CREATE TABLE #table2 (id tinyint, myType tinyint, columnB varchar(25)); INSERT INTO #table2 VALUES (1, 1, 'Match for id 1'), (1, 2, 'Second match for id 1'), (1, 4, 'Match for id 1'), (2, 1, 'Match for id 2'), (2,3, 'Not a match for id 2'), (4, 1, 'fdvg');

As you can see, all entries for id 1 exist in both tables. For id 2, #Table1 and #table2 both have type = 1, but type 2 and 3 only exist in one table (and thus should not show up in the final result). Id 3 only exists in #table1, and thus also won't show up. Id 4 only exists in #table2, and thus also shouldn't show.

Let's start by just joining the tables on id, without taking types into consideration:

SELECT * FROM #table1 JOIN #table2 ON #table1.id = #table2.id;

Result:

id myType columnA id myType columnB 1 1 aaaa 1 1 Match for id 1 1 2 aaab 1 1 Match for id 1 1 4 aaaac 1 1 Match for id 1 1 1 aaaa 1 2 Second match for id 1 1 2 aaab 1 2 Second match for id 1 1 4 aaaac 1 2 Second match for id 1 1 1 aaaa 1 4 Match for id 1 1 2 aaab 1 4 Match for id 1 1 4 aaaac 1 4 Match for id 1 2 1 bbbbb 2 1 Match for id 2 2 2 bbbbc 2 1 Match for id 2 2 1 bbbbb 2 3 Not a match for id 2 2 2 bbbbc 2 3 Not a match for id 2

As you can see, this will return a list of every row for which the ids exist in both tables, not taking types into account. This results in a result set where the combination of types are split out across rows. This is clearly not what we are after.

Next step is to add the types, and only return those rows where both the id and type exist in both tables:

SELECT * FROM #table1 JOIN #table2 ON #table1.id = #table2.id AND #table1.myType = #table2.myType;

Result:

id myType columnA id myType columnB 1 1 aaaa 1 1 Match for id 1 1 2 aaab 1 2 Second match for id 1 1 4 aaaac 1 4 Match for id 1 2 1 bbbbb 2 1 Match for id 2

Much better. Now we have a list of every combination that exists in both tables. However, as per your description, you do not want to see id = 2, because there's no full match on the types between the tables.

The above query already weeds out any rows existing in #table1 that do not exist in #table2 (id = 2, type = 2 is not returned), but obviously it did not take into account there is a row for in #table2 that does not exist in #table1.

We can accomplish that by adding a subquery listing all ids for which an entry in #table2 exists that does not exist in #table1:

SELECT * FROM #table1 JOIN #table2 ON #table1.id = #table2.id AND #table1.myType = #table2.myType WHERE #table1.id NOT IN ( SELECT #Table2.id FROM #Table2 LEFT JOIN #table1 ON #table2.id = #table1.id AND #Table2.myType = #table1.myType WHERE #table1.MyType IS NULL );

Result:

1 1 aaaa 1 1 Match for id 1 1 2 aaab 1 2 Second match for id 1 1 4 aaaac 1 4 Match for id 1

The start of the query is the same as before. However, now there is an additional WHERE clause. The subquery following that basically will give back and id for which a combination of id and type in #table2 does not have a matching record in #table1.

If you were to run that subquery by itself, it would return ids 2 and 4 (4 only exists in #Table2, where id = 2, type = 3 is not in #table1.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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 MongoDB, 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

244,852 Views