Why not fire a left join?

there is code:
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 pk = fk


as I understand it, the sample should be records with NULL because name.lucy fails the where condition. the problem is that the service does not display these values with NULL.

1|Tom|1|Moscow
1|Tom|1|Samara
3|Frank|3|Saransk


Please tell me it was the features of the sql implementation specifically in this service or I don't understand how left join works?
March 19th 20 at 08:51
1 answer
March 19th 20 at 08:53
Solution
First LEFT JOIN Cities ON citiec.fk=Names.pk

Secondly, you have a strange table, the Cities table where the primary key?
@Buford_Metz
Did the table cannot exist without a primary key? As I understand it, it is not required - eda.Kuhn commented on March 19th 20 at 08:56
@eda.Kuhn, this condition is very bad. Example of index is needed for the row ID at least, and to accelerate the search.

names:
id(auto increment,primary)|name|city_id

cities:
id(auto increment,primary)|name

SELECT * FROM names left join cities on cities.id=names.city_id
- Buford_Metz commented on March 19th 20 at 08:59
@Buford_Metz,
but my question does not concern speed up the search and beauty of code - eda.Kuhn commented on March 19th 20 at 09:02
@eda.Kuhn, for God's sake.

As joint I have already written. - Buford_Metz commented on March 19th 20 at 09:05

Find more questions by tags SQL