SQL interview a subject of transnational corporations

Posted on 2009-06-30 filed under: Oracle (RSS) 19 views

inner join
left outer join
right outer join
full outer join
A difference between the four
is a cross join is a Cartesian product of the number of rows in table another table multiplied by the number of rows
a left join b: to return to a full line - "b in the line to meet and b in the line does not meet (with null instead of)
a right join b: ibid change what is ab
to return to full join two lines in the table left join + right join
inner join table to connect only to return two out of the match

a table
1, 'x'
2, 'y'
3, 'z'

b Table
1, 'a'
4, 'b'
5, 'c'

connect with inner join:
select a.id, a.name, b.id, b.name from a, b
where a.id = b.id

1, 'x', 1, 'a'

left outer join left connected
select a.id, a.name, b.id, b.name from a
left outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null

right outer join right connections
select a.id, a.name, b.id, b.name from a
right outer join b on a.id = b.id
1, 'x', 1, 'a'
null, null, 4, 'b'
null, null, 5, 'c'

outer join full outer join
select a.id, a.name, b.id, b.name from a
full outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null
null, null, 4, 'b'
null, null, 5, 'c'