Quick Links

---

Natural Join
Cross Join

Instructor's Notes

---

Other Lectures

Search:

Powered

select c.cust_id, concat(i.fname,' ',i.lname), b.name from customer c left outer join individual i on c.cust_id = i.cust_id left outer join business b on c.cust_id = b.cust_id;

Note that we are using the "concat" function to join the two columns together.

select c.cust_id, concat(fname,' ',lname) from customer c join individual i on c.cust_id = i.cust_id union select c.cust_id, b.name from customer c join business b on c.cust_id = b.cust_id;

Now we will use a case structure. You start with "case" then say "when" and then in parenths you give your condition then you use a "then" then you give what you want to happen. Then at the end you put an 'else' and the default that happens if none of the conditions are met. Then you end it with an "end name"

select c.cust_id, case when (cust_type_cd = 'I') then concat(i.fname,' ',i.lname) when (cust_type_cd = 'B') then b.name else 'unknown' end name from customer c left outer join individual i on c.cust_id = i.cust_id left outer join business b on c.cust_id = b.cust_id;
select c.cust_id, case when c.cust_type_cd = 'I' then (select concat(i.fname,' ',i.lname) from individual i where i.cust_id = c.cust_id) when c.cust_type_cd = 'B' then (select name from business b where b.cust_id = c.cust_id) else 'unknown' end from customer c

Now see if you can use "union" to completely get rid of the case statement.

We still have two joins we need to talk about. The Natural Join and the Cross Join.

Natural Join

You will probably never use the natural join, but in case you ever see it, here is what it does.

Let's say you want to have a list of account_id and cust_id, you say

select a.account_id, c.cust_id
from account a join customer c
on a.cust_id = c.cust_id;

To do this with a natural join:

select a.accoun_id, c.cust_id
from account a natural join customer c;

Now for our last topic...

Cross Join

You have already seen something like this:

select a.account_id, c.cust_id
from account a join customer c;

wow. That's alot of rows.

In rare occasions you will need to do this. It is very powerful because it gives you all of the possible combinations of things. Now we will see a case where we need to use this.

Let's say we have accounts opening at different dates. What if the president of the company says that he wants you to create a report. It is going to start with 2004-01-01 then 2004-01-02 etc to 2004-01-31 and keep going until 2004-12-31. So every day in 2004. That's the first column. The second column should show how many accounts opened on that date. We are going to use this function:

date_add('2004-01-01', interval(1) day)

select days.dt, count(a.account_id) from account a right outer join (select date_add('2004-01-01', interval(ones.num + tens.num + hundreds.num) day) dt from (select 0 num union all select 1 num union all select 2 num union all select 3 num union all select 4 num union all select 5 num union all select 6 num union all select 7 num union all select 8 num union all select 9 num) ones cross join (select 0 num union all select 10 num union all select 20 num union all select 30 num union all select 40 num union all select 50 num union all select 60 num union all select 70 num union all select 80 num union all select 90 num) tens cross join (select 0 num union all select 100 num union all select 200 num union all select 300 num) hundreds) days on days.dt = a.open_date where days.dt < '2005-01-01' group by days.dt;

That's it. Feel free to e-mail the teacher (or myself, but I can't garauntee I can help) with any questions.

cmcculloh@gmail.com
mxxue@hotmail.com

Home - Personal - Tutorials - SQL - 3/14/06