How to join three tables in sql

In my previous article I have given different SQL joining examples.In this article i would like to give information about How to join 3 tables in SQL with examples.If you dont know the joins its really very difficult how to join 3 tables in SQL.So the main question in users mind will be How to Join 3 tables in SQL and where it is used.There are so many situations where user needs to fetch the data from two or more tables.Those are the cases where user needs to join the 3 tables.The joining 3 tables is one of the most important scenario in the SQL.

What is Join in SQL with example?

Before going to check about How to Join 3 Tables in SQL user needs to know about the concept of join with example. I have already explained the concept of SQL join in my article. In this section i just would like to give single liner definition of different join with one syntax and example.

1.Inner Join :

Inner Joins is nothing but fetching the common records from two or more tables.

Syntax :

from table1 t1,table2 t2

where t1.col=t2.col;

Example :

Select a.Department_ID,a.Department_Name from

Employee b,Department a

2.Outer Join :

Outer join in SQL is nothing but fetching the common records from two or more table and all records from either left table or right table.

There are two types of outer join in SQL :

1.Left outer Join

2.Right outer Join

Left outer Join :

Left outer join in SQL is nothing but fetching the common records from two or more tables and all records from Left table.

Syntax :

Type 1:Left Outer Join Syntax with +

from table1 t1,table2 t2

where t1.col=t2.col(+);

Type 2:With Keyword

from table1 t1 Left Outer join table2 t2

on t1.col=t2.col;

Example :

Select a.Employee_name,b.Department_Name from

Employee a,Department b

where a.Department_ID=b.Department_ID(+);

Right outer Join :

Right outer join in SQL is nothing but fetching the common records from two or more tables and all records from Right table.

Syntax :

Type 1:Right Outer Join Syntax with +

from table1 t1,table2 t2

where t1.col(+)=t2.col;

Type 2:With Keyword

from table1 t1 Right Outer join table2 t2

on t1.col=t2.col;

Example :

Select a.Employee_name,b.Department_Name from

Employee a,Department b

where a.Department_ID(+)=b.Department_ID;

The cross join is the join where each and every table value can join with every other value from other table.

Where condition is missing or wrong in Cartesian join.

Syntax :

Type 1:Right Outer Join Syntax with +

from table1 t1,table2 t2;

Example :

Select a.Employee_name,b.Department_Name from

Employee a,Department b;

How to join three tables in sql

How to Join 3 Tables in SQL Example :

In this section i would like to give you information about How to join 3 tables in SQL with real world industry example.I hope you get the common idea about how to join 2 tables with examples.There are so many ways using which user can fetch the records for multiple tables. The first and most simple way to fetch the data is using Subqueries and correlated subqueries. I would like to give you the solution got How to join 3 tables in SQL which will work in most of important database programming Languages like PostgreSQL,Microsoft SQL Server or MS SQL as well.

Syntax :

SELECT t1.column, t2.column,t3.column FROM

table1 join table2 ON table1.primarykey =table2.foreignkey

join table3 ON table2.primarykey =table3.foreignkey;

The table1 and table2 creates new temporary table. With taht temporary table the table 3 is joining. It is taking the common records from 3 tables which are table1,table2 and table3 e.t.c.

The joining of 3 tables is important scenario in most of reporting tools and techniques.In reporting user needs to fetch the data from multiple tables and using the concept of joining 3 tables user can achieve this easy way.

Real World Industry Example :

In this section i would like to explain the example of How to join the 3 tables in SQL thoroughly.I have explained the syntax of joining 3 tables in SQL in above section. If user wants the records from multiple tables then concept of joining 3 tables is important.

Let us take the example of Joining 3 tables. If user wants to join tables named Employees,Department and Salary to fetch the Employee name and salary then following queries are helpful.

SQL > SELECT * FROM Employees;
+——–+————-+
| emp_id | emp_name |
+——–+————-+
| 1001| Amit Sharma |
| 2002| Rohit Patil |
| 3003| Rajiv Shukl |
| 4004| Ram Rahim |
+——–+————-+

Lets say, Employees table fetched the list of above employees.

SQL> SELECT * FROM Department;
+———+———–+
| dept_id | emp_id |
+———+———–+
| 101 | 1001 |
| 102 | 2002 |
| 103 | 3003 |

Lets say, Department table contains dept_id and emp_id of employee.

SQL> SELECT * FROM Salary;
+——–+———+
| emp_id | salary |
+——–+———+
|1001 | 10000 |
|2002 | 15000 |
|3003 | 25000 |
|4004 | 30000 |
+——–+———+
Lets say, Salary table contains emp_id and salary of employee.

If user wants to fetch emp_name,dept_no and salary for the employees then following query will be helpful,

SQL> SELECT e.emp_name, s.salary FROM Employee e JOIN Salary s ON e.emp_id=s.emp_id JOIN Department d ON s.emp_id=d.emp_id;
+———-+———–+——–+—–+
| emp_name | dept_no | Salary |
+———-+———–+——–+
| Amit Sharma | 101 | 10000 |
| Rohit Patil | 102 | 15000 |
| Rajiv Shukl | 103 | 25000 |
| Ram Rahim | 104 | 30000 |
+———-+———–+——-+——+

This is the most important example of How to join 3 tables in SQL. User can join multiple tables with multiple real world scenarios.

I hope you like this article on How to Join 3 tables in SQL. I have explained the real world industry example with the same. If you like this article or if you have any issues or concerns with the same kindly comment it in to comments section.

I’ll explain how to join more than two tables in SQL.

But first of all, you need to be sure that your MySQL server is installed and running.

Let’s create 3 table and write a join SQL statement.

For example, we have a student table with 3 students “John”, “Henry” and “Michael”.

id name
1 John
2 Henry
3 Michael

And there are 5 available courses in the school: “Maths”, “Physics”, “Biology”, “Chemistry” and “History”.

id name
1 Maths
2 Physics
3 Biology
4 Chemistry
5 History

And we need to create a table to store references between students and courses that contains student id and course id.

student_id course_id
1 1
1 2
1 3
2 2
2 3
2 4

There are 2 types of joins in the MySQL: inner join and outer join.

The difference is outer join keeps nullable values and inner join filters it out.

So I’ll show you examples of joining 3 tables in MySQL for both types of join.

How To Inner Join Multiple Tables

I want to select all students and their courses.

So we need to write MySQL query to take the data from multiple tables.

That’s an example how to join 3 tables in MySQL.

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to combine two or more tables into one in Word document?

In Word, if you have two or more tables needed to be merged into one single table as below screenshot shown, how can you quickly handle it?
How to join three tables in sql

You can drag the table to join with another one.

1. Click at anywhere of the table you want to drag, then the cross sign will be appeared.

How to join three tables in sql

2. Then click on this cross sign to select the whole table, and drag it to place near the table you want to join to.
How to join three tables in sql

3. Release the cursor, now the table has been joined to the above one.
How to join three tables in sql

Also, you can use the Merge table command in context menu to merge two tables.

1. Click at anywhere of the table you want to drag, then the cross sign will be appeared, then select the cross sign to select the whole table.

How to join three tables in sql

2. Press Ctrl + X to cut the table, then put the cursor at the place of the table you want to insert the cut table, right click to select Merge table from the context menu.
How to join three tables in sql

Now the cut table has been inserted into the table you want.
How to join three tables in sql

The Shortcuts can combine tables too.

Shift + Alt + Up arrow to combine with above table

1. Click on the table you want to drag, then the cross sign will be appeared, then click the cross sign to select the whole table.

How to join three tables in sql

2. Press Shift + Alt + Up arrow until the selected table is joined to above one.
How to join three tables in sql

Shift + Alt + Down arrow to combine with below table

1. Click at anywhere of the table you want to drag, then the cross sign will be appeared, then click the cross sign to select the whole table.

How to join three tables in sql

2. Press Shift + Alt + Down arrow until the table is joined to below one.
How to join three tables in sql

Tabbed browsing & editing multiple Word documents/Excel workbooks as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Word documents or Excel workbooks in one Word window or Excel window, and easily switch between them by clicking their tabs.
Click for free trial of Office Tab!

In this page, we are going to discuss such a join which involves the participation of three tables and there is a parent-child relationship between these tables.

A parent-child relationship between two tables can be created only when there is a PRIMARY KEY in one table and FOREIGN KEY in another table.

Syntax diagram – SQL JOIN of three tables

How to join three tables in sql

Example: SQL JOIN – three or more tables

Here is an example of SQL join three tables with conditions.

Sample table: agents

Sample table: customer

Sample table: orders

To get ‘ord_num’ and ‘cust_code’ columns from the table ‘orders’ and ‘cust_name’ and ‘cust_city’ columns from the table ‘customer’ and ‘agent_code’ column from the table ‘agents’ after a joining, with following conditions –

1. ‘a’, ‘b’ and ‘c’ are the aliases of ‘orders’, ‘customer’ and ‘agents’ table,

2. ‘orders’ and ‘customer’ tables are child table of ‘agents’ table because ‘agent_code’ is primary key in ‘agents’ table and foreign key in ‘customer’ and ‘orders’ table,

3. ‘orders’ table is child table of ‘customer’ table because ‘cust_code’ is primary key in ‘customer’ table and foreign key in ‘orders’ table,

4. ‘cust_city’ and ‘working_area’ of ‘customer’ and ‘agents’ table must be same,

5. ‘cust_code’ of ‘orders’ and ‘customer’ table must be same,

6. ‘agent_code’ of ‘orders’ and ‘agents’ table must be same,

the following SQL statement can be used :

SQL Code:

Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

SQL: Interview question/Tips of the Day

Disadvantages of DBMS

1. Complexity: The provision of the functionality that is expected of a good DBMS makes the DBMS an extremely complex piece of software. Failure to understand the system can lead to bad design decisions, which can have serious consequences for an organization.

2. Size: The complexity and breadth of functionality makes the DBMS an extremely large piece of software, occupying many megabytes of disk space and requiring substantial amounts of memory to run efficiently.

3. Performance: The DBMS file based system is written to be more general, to cater for many applications rather than just one. The effect is that some applications may not run as fast as they used to.

4. Higher impact of a failure: The centralization of resources increases the vulnerability of the system. Since all users and applications rely on the

vailabi1ity of the DBMS, the failure of any component can bring operations to a halt.

5. Cost of DBMS: The cost of DBMS varies significantly, depending on the environment and functionality provided. There is also the recurrent annual maintenance cost.

6. Additional Hardware costs: To achieve the required performance it may be necessary to purchase a larger machine, perhaps even a machine dedicated to running the DBMS. The procurement of additional hardware results in further expenditure.

7. Cost of Conversion: In some situations, the cost of DBMS and extra hardware may be insignificant compared with the cost of converting existing applications to run on the new DBMS and hardware. This cost is one of the main reasons why some organizations feel tied to their current systems and cannot switch to modern database technology.

I have 3 tables listing below:

I wrote a query as shown below:

My desire output is as shown below:

However the query show me the below result:

Is it something wrong on my query? Please advice.

3 Answers 3

You haven’t joined all of the common keys, so you are getting Cartesian results. You needs to join a to c with header id, like so

How to join three tables in sql

You need to learn to use proper explicit join syntax. A simple rule: never use commas in the from clause. Always use explicit join s:

SELECT a.order_number, a.header_id, b.line_id, b.quantity ,c.hold_price_id,c.released_flag FROM Table_A a cross JOIN Table_B b cross join table_C c;

ORDER_NUMBER HEADER_ID LINE_ID QUANTITY HOLD_PRICE_ID RELEASED_F

9 rows selected.

Not the answer you’re looking for? Browse other questions tagged sql oracle join or ask your own question.

Linked

Related

Hot Network Questions

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

  • Blog
  • Facebook
  • Twitter
  • LinkedIn
  • Instagram

site design / logo © 2021 Stack Exchange Inc; user contributions licensed under cc by-sa. rev 2021.4.1.38970

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let’s look at a selection from the “Orders” table:

OrderID CustomerID OrderDate
10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20

Then, look at a selection from the “Customers” table:

CustomerID CustomerName ContactName Country
1 Alfreds Futterkiste Maria Anders Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Mexico
3 Antonio Moreno Taquería Antonio Moreno Mexico

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

Then, we can create the following SQL statement (that contains an INNER JOIN ), that selects records that have matching values in both tables:

Example

and it will produce something like this:

OrderID CustomerName OrderDate
10308 Ana Trujillo Emparedados y helados 9/18/1996
10365 Antonio Moreno Taquería 11/27/1996
10383 Around the Horn 12/16/1996
10355 Around the Horn 11/15/1996
10278 Berglunds snabbköp 8/12/1996

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

  • (INNER) JOIN : Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN : Returns all records when there is a match in either left or right table

Do the tables all have the same structure? This will make a difference as to which approach to take, but if ID is the only common variable then the code you need is :

create table new as select *

You will get 2 warning messages saying that variable id already exists, due to the lazy approach of using select *. You’ll get the same message for any other variables that exist in more than one dataset, in which case you should explicitly list the variables and table source in the select statement.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Do the tables all have the same structure? This will make a difference as to which approach to take, but if ID is the only common variable then the code you need is :

create table new as select *

You will get 2 warning messages saying that variable id already exists, due to the lazy approach of using select *. You’ll get the same message for any other variables that exist in more than one dataset, in which case you should explicitly list the variables and table source in the select statement.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

In a case like this you should provide the log.

When you say it does not work – do you get an error, or just not what you expected?

And stab in the dark – are a.id, b.id and c.id all of the same type (numeric or character)?

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

Yes. The log said missing an “ON” and statement will be ignored.

  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

In answer to your question ” what’s wrong with the code “, looking at the documentation for joining tables in SQL:

It looks like the full join is for two tables :

“A full outer join, specified with the keywords FULL JOIN and ON, has all the rows from the Cartesian product of the two tables for which the SQL expression is true, plus rows from each table that do not match any row in the other table.”

You will need to modify your approach if you want to join 3 tables.

How to join three tables in sqlSometimes you ponder which SQL syntax to use to combine data that spans over multiple tables. JOIN is a syntax often used to combine and consolidate one or more tables. Tables are joined two at a time making a new table which contains all possible combinations of rows from the original two tables.

As a developer you may have different approaches towards handling database queries. MySQL Database For Beginners elaborates on how to join tables. Here are some of the more frequently used methods for consolidating queries on multiple tables into a single statement. While some of these options may affect performance, may increase or decrease the processing time, may pop up errors, but gradually through practice and experience you will know when to use which type of query.

If you need a refresher course on SQL coding and syntax, then SQL Database for Beginners and Learn SQL in 6 days are great courses to check out. They will guide you on how to perform operations on databases, how to perform Insert and Deletes in MS SQL, and how to insert data into tables. If you are new to writing SQL queries and want to learn how to build applications or generate business reports using SQL, then Introduction to SQL Training is a perfect match for you.

Methods to Join Multiple Tables

One simple way to query multiple tables is to use a simple SELECT statement. You can call more than one table by using the FROM clause to combine results from multiple tables.

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

The UNION statement is another way to return information from multiple tables with a single query. The UNION statement allows you to perform queries against several tables and return the results in a consolidated set, as in the following example:

SELECT column1, column2, column3 FROM table1

UNION SELECT column1, column2, column3 FROM table2;

This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used. UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code. As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use they can save a lot of time.

SQL JOIN

With SELECT and UNION, some databases may have a limit on the number of tables that can be handled. That’s when it is a good idea to use the SQL JOIN statement to join two or more tables.

Types of SQL JOINS

Following are the different types of SQL joins:

  • SQL INNER JOIN (or sometimes called simple join)
  • SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
  • SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
  • SQL FULL OUTER JOIN (or sometimes called FULL JOIN)
  • SQL CROSS JOIN

SQL INNER JOIN:

Definition: This returns all rows from multiple tables where the join condition is met or returns the records where table1 and table2 intersect. An INNER JOIN will only return rows for which there is data in both the tables. It specifies the complete cross product of two tables.

INNER JOIN table2

ON table1.column = table2.column;

SQL LEFT OUTER JOIN:

Definition: This type of join returns all rows from the LEFT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. This statement returns all records from table1 and only those records from table2 that intersect with table1.

LEFT [OUTER] JOIN table2

ON table1.column = table2.column;

SQL RIGHT OUTER JOIN:

Description: This type of join returns all rows from the RIGHT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. In other words, this statement returns all records from table2 and only those records from table1 that intersect with table2.

RIGHT [OUTER] JOIN table2

ON table1.column = table2.column;

SQL FULL OUTER JOIN:

Definition: This type of join returns all rows from the LEFT-hand table and RIGHT-hand table. Where the join condition is not met, nulls are inserted. The result after the JOIN operation will be all the records from both table1 and table2.

FULL [OUTER] JOIN table2

ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

SQL CROSS JOIN:

This will return all records where each row from the first table is combined with each row from the second table. A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

SELECT * FROM [TABLE 1], [TABLE 2]

Summary

Your success as a successful DBA lies in structuring and developing a properly designed and linked database, which will help you to retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like. Also remember that an important factor about subqueries is performance. Each query is processed separately in full before being used as a resource for your primary query. So creative use of JOIN statements may provide better results with a lesser lag time.