SQL queries allow us to form relationships between multiple tables and retrieve data from that table. The process of querying multiple tables is called Joins and it is an important feature of RDBMS .(SQL initially pairs every row in one table with every row in the other table. This causes retrieving of a large number of rows.)
Joins are mainly classified in to 3 different types.
Simple Join
It is the most common type and it retrieves rows from 2 tables having a common column.
a.Equi Join –
This consists of comparing the columns in both tables to be joined for equality (ie the operator used in the where clause is ‘=’)
b.Non-Equi Join –
A non equi join specifies the relationships between columns belonging to different tables by making use of the relational operators (<,>,<=,>=,<>) other than =.
Eg:Select Name,Class from Tab1.No<Tab2.No
(Note:- The number of join conditions should be exactly one less than the number of tables in the from clause.When two or more tables have the same column name ,the column name is prefix with table name.)
Table Aliases
To prevent the ambiguity in a query we include the table names in the select statements. If the table names are lengthy we can use a pen name (alias name) for avoiding difficulty.
Eg.Select A .No,A.Name ,B.Class from Tab1 A,Tab2 B where A.No=B.No;
Self-Join
A table is join to itself.
Eg. Select A.No,A.Name,B.Name from Tab1 A,Tab1 B where A.No=B.No;
Outer-Join
They generate a query result that returns all rows returned by the simple join as well as those rows from one table that do not match any row from the other table. The symbol (+) represents outer join
Eg: Select Name, Class from Tab1, Tab2 where Tab1.No=Tab2.no(+);
Views
View is not stored as a physical table .Only its definition is saved . (So view is called virtual tables).
Eg..Create view V1 as select name, class from Example;
(Note – Here any changes in V1 and Example is affected each other)
Create view V1 as select class ,sum (mark) “Total” from Example group by class ;
(Note –Here we can’t alter the view V1.ie.The views created with group by ,subquerries ,aggregate functions, etc are not updateable).
Drop view view name;
For removing a view from database;
(Note – Create table Eg as select * from Example; )
Create table Eg as select Name,Designation from Example;
Here first case creates a carbon copy of the table Example, and second case creates a table with Name and Designation fields)