0

I am quite new in mysql. I have to join two tables

Lets call them table_A and Table_B

I learnt to inner join tables using the following code

SELECT *
FROM table_A a 
INNER JOIN table_B b
ON a.criteria_1 = b.criteria_1
AND a.criteria_2 = b.criteria_2
AND a.criteria_3 = b.criteria_3

Today I came across a query which uses multiple inner joins

SELECT *
FROM table_A a 
INNER JOIN table_B b
ON a.criteria_1 = b.criteria_1
INNER JOIN table_B b1
ON a.criteria_2 = b1.criteria_2
INNER JOIN table_B b2
ON a.criteria_3 = b3.criteria_3

What is the difference between both of these?

This is the first time I am writing a question here.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Mohamed Amar
  • 11
  • 1
  • 1
    Start by reading a definition join in SQL. Work through an example. Run code part by part. Eg at sqlfiddle.com. This is a "self-join". Read about that. – philipxy Nov 12 '17 at 08:16
  • difference is it will make your query unmanageable. and lots of join will make your query's execution time higher. you can test both of the query's execution time and you will get the difference. – Newaz Sharif Nov 12 '17 at 08:19
  • @NewazSharif: there is actual difference in functionality too. – Sergio Tulentsev Nov 12 '17 at 08:20
  • ok. I got the point. result will be different also. thanks. @ Sergio Tulentsev. – Newaz Sharif Nov 12 '17 at 08:21
  • @philipxy RE the proposed dup target. I know what a self-join is, but I think I wouldn't be able to get it from those answers if I didn't. Perhaps, there's a better, more upvoted explanation. – Sergio Tulentsev Nov 12 '17 at 08:21
  • @SergioTulentsev It is one thing to talk about "what a self-join is" in terms of syntax or the result of operators. In those terms it's clear what they are & do--why would anyone need to ask "what is the difference"?. The question is *why* does one inner join, and *why* would one write one thing rather than another--what is the difference in terms what a query asks in business terms. Indeed, why would one ever write any particular thing? Because of what queries *mean*. And that is what my answer at the duplicate link *explains*. However, appreciating it requires more that 4 minutes' thought. – philipxy Nov 12 '17 at 08:34
  • "Difference between" is seldom a helpful term in a technical discussion. It is an everyday word. What could even possibly be "not different" between these things? There's no shared structure here between definitions or syntax. If you mean, how does each work, just say so. But that's two (too-broad) questions, that are answered by any definition/introduction/tutorial on joins. And you'll get (duplicates of previous) answers that are just examples & definitions of each. If you want something in between, pin down what you mean and explain yourself. – philipxy Nov 12 '17 at 20:12
  • 1
    @SergioTulentsev PS Unfortunately I would be very surprised if there were an SO answer that does something other than reiterate in terms of table operators & values what the code already says. (Not that this question is clear; see my comment re "difference".) Or even a textbook. Relational expositions re semantics are poor--they throw out example answers to example queries & hope that you "get it". I gave that duplicate because of my answer to it. The question was posted in relational-algebra, a low-traffic tag. (But the Q & A both address SQL too.) I just tagged the duplicate with 'sql'.) – philipxy Nov 12 '17 at 21:37

1 Answers1

2

Couple of differences , 1st query will only return 6 columns 2nd will return 12, !st query all criteria have to match 2nd query any can. 

DROP TABLE IF EXISTS A,B;

CREATE TABLE A (C1 INT, C2 INT, C3 INT);
CREATE TABLE B (C1 INT, C2 INT, C3 INT);

INSERT INTO A VALUES (1,1,1),(1,2,4);
INSERT INTO B VALUES (1,1,1),(1,2,3);

SELECT * FROM
A
JOIN B ON A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3;

SELECT * 
FROM A 
JOIN B B1 ON A.C1 = B1.C1 
JOIN B B2 ON A.C2 = B2.C2 
JOIN B B3 ON A.C3 = B3.C3;

SELECT * FROM
    -> A
    -> JOIN B ON A.C1 = B.C1 AND A.C2 = B.C2 AND A.C3 = B.C3;
+------+------+------+------+------+------+
| C1   | C2   | C3   | C1   | C2   | C3   |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> SELECT *
    -> FROM A
    -> JOIN B B1 ON A.C1 = B1.C1
    -> JOIN B B2 ON A.C2 = B2.C2
    -> JOIN B B3 ON A.C3 = B3.C3;
+------+------+------+------+------+------+------+------+------+------+------+------+
| C1   | C2   | C3   | C1   | C2   | C3   | C1   | C2   | C3   | C1   | C2   | C3   |
+------+------+------+------+------+------+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |    1 |
|    1 |    1 |    1 |    1 |    2 |    3 |    1 |    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+------+------+------+------+------+------+------+
2 rows in set (0.00 sec)
P.Salmon
  • 17,104
  • 2
  • 12
  • 19