Joins:
When data is required from more than
one table or view then one table or view should be join with other table or
view in the means of columns. Then the
columns in different tables or views should match in their data types and data.
Columns names are may or may not be equal.
The joins are performed
when FROM clause contains more than one table or view in their SQL query. If FROM
clause contains N tables then the minimum requirement is N-1 JOINS.
IMPORTANT
RULES ABOUT JOINS:
·
The
SELECT clause columns are may or
may not present in JOIN statement.
·
If
any columns are contains LOB data types then that columns are should not
be used in WHERE clause statement.
·
The
optimizer determines the order in which join the table based on “given join
conditions and Indexes upon the tables”.
·
If
more than one table or view contains
the same column name then optimizer may confused which table column you want.
Use “.” Operator for representing the column name with table name like TABLE
NAME.COLUMN NAME.
·
In
normal conditions also if you are using column name in select statement with the
help of table name then data access is enhanced compared to normal one.
Ex:
Consider two table like employee (employee no, employee name,
manager, salary ,hire date, commission ,department no) and department (department no, department
name, location) . We want a result which displays the employee number, salary,
department no, department name, location.
Then we must combine these
both tables and produce a result without
a duplicate record.
SQL >
SELECT employee number, salary, department
no, department name, location FROM employee, department;
This query display the result in
Cartesian product. That is if employee and department tables contains 14 and 4
records then result will be 14 X 4 = 56 records.
This can be eliminated by using
the JOINS
SQL >
SELECT employee number, salary, department
no, department name, location FROM employee E, department D WHERE E.
department no= D . department no;
NOTE: The columns should match in their
data types and data. Columns names may or may not be equal.
There are
different types of Joins are present in SQL.
EQUI
JOIN:
The join is based upon the data type
and data present in the columns those are used in join statement. The columns
names are may or may not be equal. The EQUI join is done by using the
equal operator “=”.
SYNTAX:
SELECT col1, col2,………..
FROM table1, table2
WHERE table1.col=table2.col
EX:
1.
SQL
>
SELECT employee number, salary, department
no, department name, location FROM employee E, department D WHERE E.
department no= D . department no;
When you are
using the same data type and data but one column data is different from other
column data of different tables then join performed but result will not be
displayed.
EX:
SQL>
SELECT employee name,
employee no, department name, location
FROM employee
E, department D
WHERE
E. employee name= D. department name;
In the above example the data
type and data are similar but data is not relate to one to other. In this
situation EQUI join performs but “no records are selected”
feedback is given by the server.
NON EQUI JOIN:
The Non Equi join combines two tables data in usage of
relational operator other than “=”. The NON EUQI performs only when one table column data is falls in the
range of other table columns data. It uses the between operator so it is
also called “BETWEEN JOIN”.
SYNTAX:
SELECT col1, col2,………..
FROM table1, table2
WHERE table1.col BETWEEN table2.col1
AND table2.col2
Ex:
Consider Salary grade is another table it contains
SALARY GRADE(grade, low salary, high salary).
SQL>
SELECT employee
name, employee no, salary, grade
FROM employee E, salary grade s
WHERE E. salary between s. low salary and s.
high salary;
NOTE:
Here “AND” is not logical operator .it is simply
operator with combination of “BETWEEN”.
No comments:
Post a Comment