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