Understanding the working of JOINS

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

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

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

ORA-01031: insufficient privileges

In a scenario when there are three user U1, U2, U3. User 1 have create a table and granted select access to user 2.
User 2 have create a view on this table and sysdba have granted select access on this view to User 3.

Now user 3 have access to select date from view create by user 2 because access is granted by sysdba, but even then User 3 can not select from the view and getting error insufficient privileges.

Explanation:
U2 can select from the table hence U2 can create the view on it and select from the view, but when U3 tries to select from the view it checks as U2 and see if U2 can grant select access on the underlying table which is owned by U1.

Because U1 just granted select access to U2 but did not mention "WITH GRANT OPTION" hence U3 can not select from the view and getting the permission error.

To fix the issue grant select to U2 on table create by U1 with "WITH GRANT OPTION" and this will fix the issue.

The example is described as below:

SQL> create user &user identified by &pass default tablespace harvey
SQL> temporary tablespace temp2;
Enter value for user: u1
Enter value for pass: u1
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u1 identified by u1 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u2
Enter value for pass: u2
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u2 identified by u2 default tablespace harvey temporary tablespace temp2
User created.
SQL> /
Enter value for user: u3
Enter value for pass: u3
old   1: create user &user identified by &pass default tablespace harvey temporary tablespace temp2
new   1: create user u3 identified by u3 default tablespace harvey temporary tablespace temp2
User created.

SQL> conn / as sysdba
Connected.
SQL> create table u1.t1 as select * from scott.emp
  2  /
Table created.
SQL> conn u1/u1
Connected.
SQL> select count(*) from t1;
  COUNT(*)
----------
        14
SQL> grant select on t1 to u2;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> create view v2t1 as select * from u1.t1;
View created.
SQL>
SQL> grant select on v2t1 to u3;
grant select on v2t1 to u3
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'U1.T1'

SQL> conn / as sysdba
Connected.
SQL> grant select on u2.v2t1 to u3;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
select count(*) from u2.v2t1
                        *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn u1/u1
Connected.
SQL> grant select on t1 to u2 with grant option;
Grant succeeded.
SQL> conn u3/u3
Connected.
SQL> select count(*) from u2.v2t1;
  COUNT(*)
----------
        14