Freedo !

 继结束了员工座椅的投票之后，出炉了 "food tasting" invitation, 让员工自己选择食品供应商。

It's about "LEFT JOIN" in SQL query.
I would like to blog it by 4 steps:

1. What's Left Join?
2. How to implement?
3. Common errors.
4. Some practise.

If you are seeking for an answer from internet for what’s going wrong with your SQL left join, you may try luck by directly go to item 3. If you would like learn something about left join, let’s start from the beginning.

1. What's Left Join?

Firstly explain it to a 10 years old boy about what’s left join.

Class you in, some mates have white paper on hands; The next class have some color pens on hands.
Now the teacher is asking you to: find out how many mates in your class with paper already find their partners with a pen, so they can play painting and how many mates left don’t have paper on hands so the teacher could get for them.
Now what are you going to say:
Hey, all of the us please stand in a queue, all of the next door buddies with pens on hands please come here find you partners with paper on hand.
Look, the words you are saying is exactly the “left join” command in SQL try to do, to match staffs from different places.

Secondly explain in programming:
"Select" in SQL is pretty much a function in programming language, with the indicated data sources generate into one & only one output.
The role left join plays here is to match different data sources, merge them into 1.

2. How to implement?

Now let’s back into the reality, as a DBA for an online store, you have 3 tables:
Table users for recording userid, username;
Table selling for recording users consume info, how much money they spent;
Table visits for recording how many times they visit the web after registration.
These 3 tables are just samples, doesn’t apply the 3NF checking :)

Userid username
1     Jack
2     Rose
3     Tom

table selling
Userid amount
1     100
3     150
1     200

table  visits
Userid visitcounts
1     5
4     2
3     4

Now, you boss comes to you, “give me the report of how many times the users visited our web”.

With left join, a piece of cake:
You do.. “ select users.username,visits.visitcounts from users left join visits on users.userid = visits.userid “

(No idea about left join syntax? Here you go:
http://msdn.microsoft.com/en-us/library/bb208894.aspx
http://technet.microsoft.com/en-us/library/ms190014.aspx
http://dev.mysql.com/doc/refman/5.0/en/join.html
)

Then you will get:
username  visitcount
jack     5
rose     NULL
tom     4

How does this happen?

select users.username,visits.visitcount from users left join visits on users.userid = visits.userid
The left table is on the left of the syntax; Right is on the right (visits here).
1. get all the data from the left table

2. pull out the date from right table

3. match them together, keep the matched data from right table, screen out none-matched

So you back and found in table selling there are multiple records for some users, how could left join work without 1 on 1 match?
Got stuck?

Try this out:
select users.username, selling.amount from users left join ( select userid,sum(amount) as amount from selling group by userid ) selling on users.userid = selling.userid

Here’s what you get:

username  amount
jack      300
rose      NULL
tom      150

See? By doing the sub-query, { select some-date from a-left-table left join ( select another-data from another-table ) another-table on condition-leftside = condition-rightside }, which makes the left join quite powerful.

To help understand the formula, separate it into several parts:
Select {  0  }  <= input the data/columns you want to get eventually in here
From { 1 }   <= input the left table
Left join { 2 }  <= input the data going to be matched to left table, sub-query is workable inside
On { 3 }   <= the condition going to match between each other
Where/order by / limit { 4 }  <= the condition you going to add into left table when pulling data out of there.

3. trouble shooting:

1. reported “Unknown column '' in 'on clause' error.
This is common, must because you omit something.

Take a look at following syntax, which will report above error:

SELECT users.username, selling.amount
FROM users
LEFT JOIN (
SELECT sum( amount ) AS amount
FROM selling
GROUP BY userid
)
selling ON users.userid = selling.userid

Here will report Unknown column 'selling.userid' in 'on clause'.
People tend to think, since not going to show the userid in final report/output, so not necessary to select it, that’s partially right, look at the matching condition, users.userid = selling.userid, how could the function find the variable “selling.userid” since it never been claimed ?
So, to correct it, add select userid before sum(amount).

Check this out:
SELECT users.username, selling.amount
FROM users
LEFT JOIN (SELECT userid, sum( amount ) AS amount )
FROM selling
GROUP BY userid selling ON users.userid = selling.userid

Will report syntax error.
Properly using the parentheses following left join, which should include the full syntax which helps pull out the data from another side, i.e.

(
SELECT sum( amount ) AS amount
FROM selling
GROUP BY userid
)

And also parentheses is required doing such kind of sub-query.

SELECT users.username, selling.amount
FROM users
LEFT JOIN SELECT userid, sum( amount ) AS amount
FROM selling
GROUP BY userid AS selling ON users.userid = selling.userid

Syntax error, if you want to use “as”, use it thoroughly.

error Column '' in on clause is ambiguous

select users.username, selling.amount from users left join ( select userid,sum(amount) as amount from selling group by userid ) selling on users.userid = userid

Very easy to understand, because you omit the users.userid = selling.userid

It’s all about good habits on coding.

* Homework:

How to get:
username  visitcounts
jack      5
tom      4

How to get:
username  visitcounts
jack      5
tom      4
NULL     2

Practise following commands:
"join/inner join/cross join/straight_join/left outer/right outer”.

P.S. This is my first blog purly talking about technical staffs, and they are my personal opinions.
The reason that I didn't do that before it's because my job, so I won't use it in interviewing :)
The reason for me talking about the "left join" staff is because I can't find the right thing I need from the web, so made myself one.

Your comments welcome.