DataBase Basic1 (Inner and outer join)

Que: What is Referential integrity?
Ans: Referential integrity is database principle which ensure references between data is valid and intact. It prevents user/application to enter invalid data into database.

Que: What is difference between inner join and outer join?
Que: We will try to understand the difference by an example. Suppose we have 2 tables:
Table A              Table B
Id   Name           Id   Name
1    Pankaj          1    Subbu
2    Aman            2    Pankaj
3    Abhay           3    Anil
4    Manish          4    Abhay
Inner Join:It is the intersection of the tables i.e. the rows they have in common.
So the following inner join query will return the result as follows:

SELECT * FROM TableA INNER JOIN TableB
ON TableA.Name = TableB.Name

id   name          id   name
1   Pankaj        2    Pankaj
3   Abhay         4    Abhay

Left Outer Join:
A left outer join will give all rows in A, plus any common rows in B.

SELECT * FROM TableA LEFT OUTER JOIN TableB
ON TableA.Name = TableB.Name

id      name          id        name
1      Pankaj        2         Pankaj
2      Aman          null       null
3      Abhay         4         Abhay
4      Manish        null        null

Full outer join:
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B.

SELECT * FROM TableA FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id      name          id      name
1       Pankaj       2       Pankaj
2       Aman        null    null
3       Abhay       4       Abhay
4      Manish      null     null
null     null           1      Subbu
null     null           3      Anil

Que: Write a query to achieve following result.
1)

Ans:  SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name

2)

Ans:  SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name

3)

Ans:  SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name

4)

Ans: SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
        WHERE TableB.id IS null

5)

Ans: SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name
         WHERE TableA.id IS null  OR TableB.id IS null