Union & Joins

UNION:

Ø  UNION is used to concatenate rows into one table from single or multiple tables.

Ø  Rules :  no. of columns & data type of both the queries must be same. column may be different

Ø  UNION can be used to eliminate duplicate rows

Ø  UNION ALL retrieved duplicate rows also.

SELECT CNO,CNAME FROM CUST WHERE CNO=10

UNION/UNIONALL

SELECT CNO,CNAME FROM ITEM WHERE INO=20

JOINS:

Ø  JOINS can be used to concatenate columns from one table or multiple tables.

Ø  JOIN types are :

1.      left outer join :  which can be used to retrieve matching, non matching rows from leftside table

2.      right outer join: which can be used to retrieve matching, non matching rows from right side table.

3.      full outer join: which can be used to retrieve matching, non matching rows from both the tables.

4.      self join or inner join : can be achieved by defining alias for the table.

   EXPLAIN :

    It can be used to evaluate the performance of  SQL queries.

    It can be used to tune SQL queries.

    Input is SQL queries and output is plan-table.

    For every SQL query one plan-table will generate.

    All plan-tables are stored in physical seq file.