How To Bring Together Iii Tables Inwards Sql Enquiry – Mysql Example

Three tabular array JOIN Example SQL

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

In the instance of joining 3 tables table, 1 relates to tabular array 2 together with hence tabular array 2 relates to tabular array 3. If yous hold off at closely yous discovery that tabular array 2 is a joining tabular array which contains primary key from both tabular array 1 together with tabular array 2. As I said it tin endure extremely confusing to empathise bring together of 3 or to a greater extent than tables.

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

SQL Join is too a real pop theme inward SQL interviews together with at that topographic point are e'er been approximately questions from Joins, similar the departure betwixt INNER together with OUTER JOIN,  SQL query with JOIN e.g. Employee Department relationship and  Difference betwixt LEFT together with RIGHT OUTER JOIN etc. In short, this is i of the most of import topics inward SQL both from sense together with interview signal of view.

Btw, the alone agency to master copy SQL bring together is doing every bit much do every bit possible. If yous could solve most of SQL puzzles from Joe Celko's classic book, SQL Puzzles, together with Answers, sec edition, yous volition to a greater extent than confident most dealing with SQL joins, whether it could endure 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, together with 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 offset bring together tabular array 1 together with tabular array 2 which attain a temporary table with combined information from table1 together with table2,  which is hence joined to table3. This formula tin endure extended to to a greater extent than than 3 tables to northward tables, You merely involve to brand certain that SQL query should accept N-1 bring together disputation inward social club to bring together northward tables. similar for joining 2 tables nosotros require 1 bring together disputation together with for joining 3 tables nosotros involve 2 bring together statement.

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

SQL Query to JOIN 3 tables inward MySQL

In social club to meliorate empathise joining of 3 tables inward SQL query let's encounter an example.  Consider the pop illustration of Employee together with 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 together with 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 lift together with subdivision name amongst nosotros involve to join 3 tables. First JOIN disputation volition bring together Employee together with Register together with do a temporary tabular array which volition accept dept_id every bit approximately other column. Now minute 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 together with it tin endure 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 yous wish to empathise it fifty-fifty meliorate than crusade joining tables measuring past times step. So instead of joining 3 tables inward i go, offset bring together 2 tables together with encounter how the outcome tabular array volition hold off 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 together with it volition function inward approximately other relational database every bit good e.g. Oracle, SQL Server, Sybase, PostgreSQL etc. Let us know if yous aspect upward whatever number land running this 3 tabular array JOIN query inward whatever other database.


Further Learning
What is departure betwixt correlated together with noncorrelated subqueries inward SQL

Difference betwixt clustered together with nonclustered index inward SQL

What is ACID properties of transaction inward a database

When to role truncate over delete inward SQL query

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

Thanks for reading this article hence far, if yous similar this article hence delight percentage with your friends together with colleagues. If yous accept whatever questions, suggestions or dubiety hence delight driblet a comment together with I'll crusade to respond your question. 

Bagikan Artikel ini