How To Bring Together 3 Tables Inwards Sql Inquiry – Mysql Example

Three tabular array JOIN Example SQL

Joining 3 tables inward unmarried SQL query tin hold upwards real tricky if you lot are non expert with the concept of SQL Join. SQL Joins accept ever been tricky non solely for novel programmers but for many others,  who are inward programming too SQL for to a greater extent than than 2 to 3 years. There are plenty to confuse mortal on SQL JOIN ranging from diverse types of SQL JOIN similar INNER too OUTER join, LEFT too RIGHT outer join, CROSS bring together etc. Between all of these fundamentals, What is most of import virtually Join is, combining multiple tables. If you lot involve information from multiple tables inward i SELECT query you lot involve to role either subquery or JOIN. Most of the times nosotros solely bring together ii tables similar Employee too Department but sometimes you lot may require joining to a greater extent than than ii tables too a pop instance is joining 3 tables inward SQL.

In the instance of joining 3 tables table, 1 relates to tabular array 2 too hence tabular array 2 relates to tabular array 3. If you lot await at closely you lot honor that tabular array 2 is a joining tabular array which contains primary key from both tabular array 1 too tabular array 2. As I said it tin hold upwards extremely confusing to sympathize bring together of 3 or to a greater extent than tables.

I accept flora that agreement tabular array human relationship as the principal key too unusual key helps to alleviate confusion than the classical matching row paradigm.

SQL Join is too a real pop topic inward SQL interviews too at that spot are ever been around questions from Joins, similar the divergence betwixt INNER too OUTER JOIN,  SQL query with JOIN e.g. Employee Department relationship and  Difference betwixt LEFT too RIGHT OUTER JOIN etc. In short, this is i of the most of import topics inward SQL both from sense too interview yell for of view.

Btw, the solely means to original SQL bring together is doing every bit much practise every bit possible. If you lot could solve most of SQL puzzles from Joe Celko's classic book, SQL Puzzles, too Answers, sec edition, you lot volition to a greater extent than confident virtually dealing with SQL joins, whether it could hold upwards two, 3 or 4 tables.

Three tabular array JOIN syntax inward SQL

Here is a full general SQL query syntax to bring together 3 or to a greater extent than table. This SQL query should function inward all major relation database e.g. MySQL, Oracle, Microsoft SQLServer, Sybase, too PostgreSQL:

SELECT t1.col, t3.col FROM table1 bring together table2 ON table1.primarykey = table2.foreignkey
                                  bring together table3 ON table2.primarykey = table3.foreignkey

We starting fourth dimension bring together tabular array 1 too tabular array 2 which gain a temporary table with combined information from table1 too table2,  which is hence joined to table3. This formula tin hold upwards extended to to a greater extent than than 3 tables to north tables, You only involve to brand certain that SQL query should accept N-1 bring together disputation inward social club to bring together north tables. similar for joining ii tables nosotros require 1 bring together disputation too for joining 3 tables nosotros involve 2 bring together statement.

Here is a overnice diagram which too shows how does dissimilar types of JOINs e.g. inner, left outer, correct outer too cross joins industrial plant inward SQL:

SQL Query to JOIN 3 tables inward MySQL

In social club to amend sympathize joining of 3 tables inward SQL query let's encounter an example.  Consider the pop representative of Employee too Department schema. In our case, nosotros accept used a link table called Register which links or relate both Employee to Department. The principal key of Employee tabular array (emp_id) is a unusual key inward Register too similarly, principal key of Department tabular array (dept_id) is a unusual key inward Register table.

In social club to write an SQL query to impress employee cite too subdivision name amongst nosotros involve to join 3 tables. First JOIN disputation volition bring together Employee too Register too do a temporary tabular array which volition accept dept_id every bit around other column. Now mo JOIN disputation volition bring together this temp tabular array with Department tabular array on dept_id to teach the desired result. Here is the consummate SELECT SQL query example to bring together 3 tables too it tin hold upwards extended to bring together to a greater extent than than 3 or north tables.

mysql> SELECT * FROM Employee;
+--------+----------+--------+
| emp_id | emp_name | salary |
+--------+----------+--------+
| 1      | James    |   2000 |
| 2      | Jack     |   4000 |
| 3      | Henry    |   6000 |
| 4      | Tom      |   8000 |
+--------+----------+--------+
4 rows IN SET (0.00 sec)

mysql> SELECT * FROM Department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101     | Sales     |
| 102     | Marketing |
| 103     | Finance   |
+---------+-----------+
3 rows IN SET (0.00 sec)

mysql> SELECT * FROM Register;
+--------+---------+
| emp_id | dept_id |
+--------+---------+
|      1 |     101 |
|      2 |     102 |
|      3 |     103 |
|      4 |     102 |
+--------+---------+
4 rows IN SET (0.00 sec)

mysql> SELECT emp_name, dept_name FROM Employee e JOIN Register r ON e.emp_id=r.emp_id JOIN Department d ON r.dept_id=d.dept_id;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| James    | Sales     |
| Jack     | Marketing |
| Henry    | Finance   |
| Tom      | Marketing |
+----------+-----------+
4 rows IN SET (0.01 sec)

If you lot wish to sympathize it fifty-fifty amend than assay joining tables measurement past times step. So instead of joining 3 tables inward i go, starting fourth dimension bring together 2 tables too encounter how the outcome tabular array volition await like. That’s all on How to bring together 3 tables inward i SQL query inward the relational database.

By the way, inward this SQL JOIN Example, nosotros accept used ANSI SQL too it volition function inward around other relational database every bit good e.g. Oracle, SQL Server, Sybase, PostgreSQL etc. Let us know if you lot confront whatsoever number field running this 3 tabular array JOIN query inward whatsoever other database.


Further Learning
What is divergence betwixt correlated too noncorrelated subqueries inward SQL

Difference betwixt clustered too nonclustered index inward SQL

What is ACID properties of transaction inward a database

When to role truncate over delete inward SQL query

List of oftentimes used MySQL Server commands
10 pop SQL queries from Interviews

Thanks for reading this article hence far, if you lot similar this article hence delight portion with your friends too colleagues. If you lot accept whatsoever questions, suggestions or doubtfulness hence delight drib a comment too I'll assay to answer your question.