CODEWITHSUNDEEP
mysqlsql
rettoryh13
rettoryh13

Reputation: 135

Why where condition is wrong?

I try use left join for 2 tables. First table have primary key, second table do not have primary key

I use common sql syntax with simple commands.

CREATE TABLE NAMES(pk integer PRIMARY KEY, Name text);
CREATE TABLE CITIES(fk integer, Name text);

INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');

INSERT INTO CITIES VALUES(1,'Moscow');
INSERT INTO CITIES VALUES(3,'Saransk');
INSERT INTO CITIES VALUES(4,'Penza');
INSERT INTO CITIES VALUES(1,'Samara');

SELECT * FROM NAMES LEFT JOIN CITIES WHERE NAMES.pk = CITIES.fk

I hope see records with NULL. But i see only records which condition matched: https://rextester.com/FXMPF52805

Upvotes: 0

Views: 61

Answers (2)

Sandy Elkassar
Sandy Elkassar

Reputation: 139

when you choose to right joins as "Left JOIN, right Join, etc.."

you should write the query as below

SELECT * FROM NAMES LEFT JOIN CITIES on NAMES.pk = CITIES.fk

Upvotes: 0

paradox
paradox

Reputation: 602

correct Left Join syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

so you should change WHERE to ON

 SELECT * FROM NAMES LEFT JOIN CITIES ON NAMES.pk = CITIES.fk

Upvotes: 1

Related Questions