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

Three tabular array JOIN Example SQL

Joining 3 tables inwards unmarried SQL query tin flaming live on real tricky if y'all are non proficient with the concept of SQL Join. SQL Joins convey ever been tricky non alone for novel programmers but for many others,  who are inwards programming as well as 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 as well as OUTER join, LEFT as well as RIGHT outer join, CROSS bring together etc. Between all of these fundamentals, What is most of import nigh Join is, combining multiple tables. If y'all involve information from multiple tables inwards 1 SELECT query y'all involve to role either subquery or JOIN. Most of the times nosotros alone bring together 2 tables similar Employee as well as Department but sometimes y'all may require joining to a greater extent than than 2 tables as well as a pop instance is joining 3 tables inwards SQL.

In the instance of joining 3 tables table, 1 relates to tabular array 2 as well as and thence tabular array 2 relates to tabular array 3. If y'all await at closely y'all detect that tabular array 2 is a joining tabular array which contains primary key from both tabular array 1 as well as tabular array 2. As I said it tin flaming live on extremely confusing to empathize bring together of 3 or to a greater extent than tables.

I convey constitute that agreement tabular array human relationship as the principal key as well as unusual key helps to alleviate confusion than the classical matching row paradigm.

SQL Join is equally good a real pop theme inwards SQL interviews as well as in that place are ever been about questions from Joins, similar the divergence betwixt INNER as well as OUTER JOIN,  SQL query with JOIN e.g. Employee Department relationship and  Difference betwixt LEFT as well as RIGHT OUTER JOIN etc. In short, this is 1 of the most of import topics inwards SQL both from sense as well as interview betoken of view.

Btw, the alone agency to original SQL bring together is doing equally much practise equally possible. If y'all could solve most of SQL puzzles from Joe Celko's classic book, SQL Puzzles, as well as Answers, 2nd edition, y'all volition to a greater extent than confident nigh dealing with SQL joins, whether it could live on two, 3 or 4 tables.

Three tabular array JOIN syntax inwards SQL

Here is a full general SQL query syntax to bring together 3 or to a greater extent than table. This SQL query should operate inwards all major relation database e.g. MySQL, Oracle, Microsoft SQLServer, Sybase, as well as 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 kickoff bring together tabular array 1 as well as tabular array 2 which arrive at a temporary table with combined information from table1 as well as table2,  which is as well as thence joined to table3. This formula tin flaming live on extended to to a greater extent than than 3 tables to northward tables, You only involve to brand certain that SQL query should convey N-1 bring together disputation inwards lodge to bring together northward tables. similar for joining 2 tables nosotros require 1 bring together disputation as well as for joining 3 tables nosotros involve 2 bring together statement.

Here is a squeamish diagram which equally good shows how does dissimilar types of JOINs e.g. inner, left outer, correct outer as well as cross joins plant inwards SQL:

SQL Query to JOIN 3 tables inwards MySQL

In lodge to amend empathize joining of 3 tables inwards SQL query let's run across an example.  Consider the pop instance of Employee as well as Department schema. In our case, nosotros convey 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 inwards Register as well as similarly, principal key of Department tabular array (dept_id) is a unusual key inwards Register table.

In lodge to write an SQL query to impress employee advert as well as subdivision name amongst nosotros involve to join 3 tables. First JOIN disputation volition bring together Employee as well as Register as well as do a temporary tabular array which volition convey dept_id equally about other column. Now 2d JOIN disputation volition bring together this temp tabular array with Department tabular array on dept_id to larn the desired result. Here is the consummate SELECT SQL query example to bring together 3 tables as well as it tin flaming live on extended to bring together to a greater extent than than 3 or northward 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 y'all wish to empathize it fifty-fifty amend than stimulate joining tables measuring past times step. So instead of joining 3 tables inwards 1 go, kickoff bring together 2 tables as well as run across how the number tabular array volition await like. That’s all on How to bring together 3 tables inwards 1 SQL query inwards the relational database.

By the way, inwards this SQL JOIN Example, nosotros convey used ANSI SQL as well as it volition operate inwards about other relational database equally good e.g. Oracle, SQL Server, Sybase, PostgreSQL etc. Let us know if y'all facial expression upward whatever number piece running this 3 tabular array JOIN query inwards whatever other database.


Further Learning
What is divergence betwixt correlated as well as noncorrelated subqueries inwards SQL

Difference betwixt clustered as well as nonclustered index inwards SQL

What is ACID properties of transaction inwards a database

When to role truncate over delete inwards SQL query

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

Thanks for reading this article thence far, if y'all similar this article as well as thence delight portion with your friends as well as colleagues. If y'all convey whatever questions, suggestions or doubtfulness as well as thence delight driblet a comment as well as I'll stimulate to answer your question. 

Sumber https://javarevisited.blogspot.com/

Share this post