SQL - LEFT JOIN

Question

I'm learning SQL from codecademy right now.

My 1st question.. I understood that Left join is to combine 2nd table without duplicating the condition of "ON." From the pictures below, could someone explain why I see duplicated "subscription_id" column that has been added from the 2nd table "prev_q"? I just wanted to see the column "sub_numb" to be added to the 1st table "subscriptions."

My 2nd question.. When I do JOIN in general, how do I select all from table1 and only few columns from table 2? In my head, I'm thinking something like:

SELECT *(table1),    <<<< this is the part that I'm not so sure about..
  table2.column_name1,
  table2.column_name2,

'to combine 2nd table without duplicating the condition of "ON."' is not a clear statement of what left join does. 'the column "sub_numb" to be added to the 1st table "subscriptions."' & 'select all from table1 and only few columns from table 2' are not clear descriptions of what you want. Find a definition. https://stackoverflow.com/a/46091641/3404097 Clearly say what you mean. Please also read & act on [mcve]. — philipxy, Jul 20 '18 at 23:51

score 0 · Answer 1 · answered Jul 20 '18 at 16:23

This is too long for a comment.

Your understanding of a left join seems very far from what it really is. You should go back and study some more.

A left join keeps all rows in the first (i.e. "left") table, regardless of whether the on clause evaluates to true, false, or NULL. If the on clause is not true, then all column references from the second table are given the value NULL.

Barmar · Answer 2 · 2018-07-22T20:03:05.887

0

Your understanding of LEFT JOIN is incorrect. It has nothing to do with not duplicating results. LEFT JOIN returns all the rows that match the joining condition, just like INNER JOIN does.

In addition, if any rows in the left table have no match, there will be a row for each of them in the results, with NULL values for the columns coming from the right table.

See What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited Jul 22 '18 at 20:03

answered Jul 20 '18 at 16:23

Barmar

741,623
53
500
612

Unfortunate though that it is almost entirely poor answers, including highly upvoted ones. – philipxy Jul 22 '18 at 20:45
@philipxy I don't think it's quite that bad, some of them say pretty much what I wrote. – Barmar Jul 22 '18 at 23:53
Your answer says "LEFT JOIN returns all the rows that match the joining condition"--from the rows from the cross join of the tables, but you don't say that. (The rest of your explanation is not very clearly phrased either.) GordonLinoff's answer here just gives some properties of left join, it doesn't actually say exactly what it does. All the Venn diagram explanations in the link are confused & misleading, see my comments. (The one correct use has no explanation.) The most upvoted answer is a special case. Read closely & this is all evident. Compare to my answer. – philipxy Jul 23 '18 at 00:24
@philipxy My answer assumes the reader understand the general concept of what joining does. I was not trying to explain what a join is, just what makes left join special. – Barmar Jul 23 '18 at 00:27

SQL - LEFT JOIN

2 Answers2