CODEWITHSUNDEEP
mysqlsqljoin
Sandeep Chatterjee
Sandeep Chatterjee

Reputation: 3247

Understanding LEFT JOIN inner working in MySQL

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?

Upvotes: 1

Views: 1583

Answers (2)

Jar Yit
Jar Yit

Reputation: 985

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.

Upvotes: 3

Chris Barlow
Chris Barlow

Reputation: 466

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;

Upvotes: 0

Related Questions