Understanding the Joins.
Sometimes it can be hard to understand on how to use joins, my post below may be able to help you.
SQL> select * from ant;
ID
----------
1
3
4
5
Left Outer Join:
SQL> select a.id, b.tag
2 from ant a left join bat b
3 on (a.id = b.tag)
4 order by 1;
ID TAG
---------- ----------
1
3 3
4
5
Old Method:
--------------
SQL> select a.id, b.tag
2 from ant a, bat b
3 where a.id = b.tag (+)
4 order by 1;
ID TAG
---------- ----------
1
3 3
4
5
Right Outer Join:
SQL> select a.id, b.tag
2 from ant a right outer join bat b
3 on (a.id = b.tag)
4 order by 2;
ID TAG
---------- ----------
2
3 3
7
9
Old Method:
SQL> select a.id, b.tag
2 from ant a, bat b
3 where a.id (+) = b.tag
4 order by 1;
ID TAG
---------- ----------
3 3
2
9
7
Full Outer Join:
SQL> select a.id, b.tag
2 from ant a full outer join bat b
3 on (a.id = b.tag)
4 order by 1,2;
ID TAG
---------- ----------
1
3 3
4
5
2
7
9
7 rows selected.
Cross or Cartesian Join:
if you try to do a natural join and there are no common column names and data types then it will do the Cross or Cartesian by default
else Specify CROSS JOIN in between tables.
SQL> select id, tag
2 from ant, bat
3 order by 1,2;
ID TAG
---------- ----------
1 2
1 3
1 7
1 9
3 2
3 3
3 7
3 9
4 2
4 3
4 7
4 9
5 2
5 3
5 7
5 9
16 rows selected.
SQL> select id, tag
2 from ant CROSS JOIN bat
3 order by 1,2;
ID TAG
---------- ----------
1 2
1 3
1 7
1 9
3 2
3 3
3 7
3 9
4 2
4 3
4 7
4 9
5 2
5 3
5 7
5 9
16 rows selected.
Case Study:
Show the rows from ant table which are not in bat table.
SQL> select a.id
2 from ant a left outer join bat b
3 on (a.id = b.tag)
4 where b.tag is null
5 order by 1;
ID
----------
1
4
5
Show the records from bat table which are not in ant table
SQL> select b.tag
2 from ant a right outer join bat b
3 on (a.id = b.tag)
4 where a.id is null
5 order by 1;
TAG
----------
2
7
9
Sometimes it can be hard to understand on how to use joins, my post below may be able to help you.
SQL> select * from ant;
ID
----------
1
3
4
5
SQL> select * from bat;
TAG
----------
2
3
7
9
Inner Join:
Two different ways
SQL> select id, tag
2 from ant a inner join bat b
3 on (a.id = b.tag);
ID TAG
---------- ----------
3 3
SQL>
SQL> select a.id, b.tag
2 from ant a join bat b
3 on (a.id = b.tag);
ID TAG
---------- ----------
3 3
Two different ways
SQL> select id, tag
2 from ant a inner join bat b
3 on (a.id = b.tag);
ID TAG
---------- ----------
3 3
SQL>
SQL> select a.id, b.tag
2 from ant a join bat b
3 on (a.id = b.tag);
ID TAG
---------- ----------
3 3
Left Outer Join:
SQL> select a.id, b.tag
2 from ant a left join bat b
3 on (a.id = b.tag)
4 order by 1;
ID TAG
---------- ----------
1
3 3
4
5
Old Method:
--------------
SQL> select a.id, b.tag
2 from ant a, bat b
3 where a.id = b.tag (+)
4 order by 1;
ID TAG
---------- ----------
1
3 3
4
5
Right Outer Join:
SQL> select a.id, b.tag
2 from ant a right outer join bat b
3 on (a.id = b.tag)
4 order by 2;
ID TAG
---------- ----------
2
3 3
7
9
Old Method:
SQL> select a.id, b.tag
2 from ant a, bat b
3 where a.id (+) = b.tag
4 order by 1;
ID TAG
---------- ----------
3 3
2
9
7
Full Outer Join:
SQL> select a.id, b.tag
2 from ant a full outer join bat b
3 on (a.id = b.tag)
4 order by 1,2;
ID TAG
---------- ----------
1
3 3
4
5
2
7
9
7 rows selected.
Cross or Cartesian Join:
if you try to do a natural join and there are no common column names and data types then it will do the Cross or Cartesian by default
else Specify CROSS JOIN in between tables.
SQL> select id, tag
2 from ant, bat
3 order by 1,2;
ID TAG
---------- ----------
1 2
1 3
1 7
1 9
3 2
3 3
3 7
3 9
4 2
4 3
4 7
4 9
5 2
5 3
5 7
5 9
16 rows selected.
SQL> select id, tag
2 from ant CROSS JOIN bat
3 order by 1,2;
ID TAG
---------- ----------
1 2
1 3
1 7
1 9
3 2
3 3
3 7
3 9
4 2
4 3
4 7
4 9
5 2
5 3
5 7
5 9
16 rows selected.
Show the rows from ant table which are not in bat table.
SQL> select a.id
2 from ant a left outer join bat b
3 on (a.id = b.tag)
4 where b.tag is null
5 order by 1;
ID
----------
1
4
5
SQL> select b.tag
2 from ant a right outer join bat b
3 on (a.id = b.tag)
4 where a.id is null
5 order by 1;
TAG
----------
2
7
9
2 comments:
looking at cartisian join M X N rows actually don't make much sense. Is there an example situation someone would be able to make use of all-by-all join.
There are few reasons to use CROSS JOIN.
1. Suppose we are a textile mill and create cloth in different colors. We have a table to hold colors that we can produce and a table to hold all type of cloths we can produce. There is no joining condition between CLOTHS and COLORS table. But we want to get number of cloths we can produce in all of our colors with their name and color then we will do something like
select cloth_name, color from cloths, color;
2. On an annual team meating we are playing some sort of fun game where
we want a list of all employees and all possible combination of jobs in our orgnation that the employee can do.
Once we have got this list then we will print on the paper and cut the paper into pieces so that each paper has one employee and a possible job and put them in a box.
Now we will get someone to pick a paper from box and the person will start acting on that role for 1 hour.
This will require something like
select employee_name, job_title from emplyees, jobs;
There could be other examples when we might need the corss product of two or more tables.
Post a Comment