1

I have gone through many online tutorials, even Jeff's article on SQL joins and I think I still don't understand properly how SQL joins work internally. For example, let us consider the following scenario.

I have 2 tables TableA and TableB and some dummy data.

CREATE TABLE TableA(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(500),
PRIMARY KEY(id)
)ENGINE=InnoDB;


CREATE TABLE TableB(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(500),
PRIMARY KEY(id)
)ENGINE=InnoDB;

INSERT INTO TableA(name) VALUES('A');
INSERT INTO TableA(name) VALUES('B');
INSERT INTO TableA(name) VALUES('C');
INSERT INTO TableA(name) VALUES('D');

INSERT INTO TableB(name) VALUES('x');
INSERT INTO TableB(name) VALUES('A');
INSERT INTO TableB(name) VALUES('Y');
INSERT INTO TableB(name) VALUES('C');

TableA:

enter image description here

Table B:

enter image description here

Now I am running the following query:

SELECT TableA.id, TableA.name, TableB.id, TableB.name
FROM TableA
LEFT JOIN TableB
ON TableA.name=TableB.name;

which gives me the output:

enter image description here

Question:

How exactly the data is being compared internally, retrieved and the resulting table populated?

Sandeep Chatterjee
  • 3,220
  • 9
  • 31
  • 47
  • would it be easier to understand if you saw the implicit join version of the same query? `select * from tablea, tableb where tablea.name = tableb.name` ? – pala_ Apr 16 '15 at 03:40
  • I'm not sure what you mean, or in particular what your emphasis on "internally" signifies. If you are inquiring into details of MySQL's implementation, then a MySQL-specific venue would probably be a better choice than SO. – John Bollinger Apr 16 '15 at 03:44
  • @John - I mean how exactly the comparison takes place in MySQL(since that is the one I am using)? Is the comparison logic(cartesian product?) different for different RDBMS's? Since I don't understand the concept properly, may be I phrased it incorrect. – Sandeep Chatterjee Apr 16 '15 at 04:25

2 Answers2

3

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

enter image description here

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

Community
  • 1
  • 1
Jar Yit
  • 955
  • 11
  • 22
0

When you do a left join, you will get at least one row back for each row in the table on the left hand side regardless of whether there is a corresponding row in the table on the right hand side. When there isn't a corresponding row on the right hand side, the values in the resulting table will be null for those columns.

When you change the left join to an inner join, you will not get the left hand rows rows back. That is because the join condition must be true.

It might be helpful to alias the column names so you know which ones come from tablea and which ones come from table b. Once you try that, remove the left keyword and try it again. I hope that will be helpful.

SELECT TableA.id TABA_ID, TableA.name TABA_NAME, 
       TableB.id TABB_ID, TableB.name TABB_NAME
FROM TableA
LEFT JOIN TableB
ON TableA.name=TableB.name;
Chris Barlow
  • 466
  • 4
  • 8