JOINS
Sometimes we require treating multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from all the tables. To achieve this, we have to join tables. Tables are joined on columns that have the same data type and data width in the tables. There are various types of joins. They are: -
(1) Self joins (joining a table to itself)
(2) Equi joins/ Inner joins
(3) Outer joins
(4) Not Equi joins
(5) Cartesian joins
(1) SELF JOIN
In Self-Join, a table is joined to itself by specifying the column name in the WHERE condition. Usually using the table name and dot specifies the column name. In this table name must be same.
SYNTAX: -
SELECT * FROM tablename
WHERE=
(2) EQUI JOIN/INNER JOIN
We require treating multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from the all the tables. To achieve this, we have to join tables.
In EQUI JOIN, the tables are joined on the basis of the column having same data type and same data width. Such columns is specified in the WHERE clause. The two tables are joined using equality operator (=).
SYNTAX: -
SELECT * FROM
WHERE.=.
In this the tables can be different. That is both the tables are different but have one field same on the basis of which the tables are joined.
(3) OUTER JOIN
Sometimes we might want to see the data from one table, even if there is no corresponding row in the joining table. Oracle provides the outer join mechanism for this. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that will have Null’s. The outer join symbol is placed on the side of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the no deficient table can be joined.
SYNTAX: -
SELECT * FROM
WHERE.columnname>+=.
Or
SELECT * FROM
WHERE.columnname>=.(+)
Rules to Place (+) operator
The outer join symbol (+) cannot be on both sides.
We cannot “ outer join” the same table to more than one other table in a single SELECT statement.
A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.
(4) NON-EQUI JOIN
If any other operator instead of equality operator (=) is used to join the tables in the query, it is Non-Equi Join.
SYNTAX: -
SELECT * FROM
WHERE.
.
(5) CARTESIAN JOIN
When the join condition is omitted, the result is the Cartesian join of two or more tables in which all combinations of rows will be displayed. All the rows of the first table are joined to al rows of the second table. This kind of join tends to generate a large number of rows, as it involves no condition.
This join is useful in finding all the possible combination of rows from different tables. This join does not require the tables to have common column between them.
SYNTAX: -
SELECT columnname1, columnname2…
FROM tablename1, tablename2
Sometimes we require treating multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from all the tables. To achieve this, we have to join tables. Tables are joined on columns that have the same data type and data width in the tables. There are various types of joins. They are: -
(1) Self joins (joining a table to itself)
(2) Equi joins/ Inner joins
(3) Outer joins
(4) Not Equi joins
(5) Cartesian joins
(1) SELF JOIN
In Self-Join, a table is joined to itself by specifying the column name in the WHERE condition. Usually using the table name and dot specifies the column name. In this table name must be same.
SYNTAX: -
SELECT * FROM tablename
WHERE
(2) EQUI JOIN/INNER JOIN
We require treating multiple tables as though they were a single entity. Then a single SQL sentence can manipulate data from the all the tables. To achieve this, we have to join tables.
In EQUI JOIN, the tables are joined on the basis of the column having same data type and same data width. Such columns is specified in the WHERE clause. The two tables are joined using equality operator (=).
SYNTAX: -
SELECT * FROM
WHERE
In this the tables can be different. That is both the tables are different but have one field same on the basis of which the tables are joined.
(3) OUTER JOIN
Sometimes we might want to see the data from one table, even if there is no corresponding row in the joining table. Oracle provides the outer join mechanism for this. Such rows can be forcefully selected by using the outer join symbol (+). The corresponding columns for that will have Null’s. The outer join symbol is placed on the side of the join that is deficient in information. This operator has the effect of creating one or more null rows, to which one or more rows from the no deficient table can be joined.
SYNTAX: -
SELECT * FROM
WHERE
Or
SELECT * FROM
WHERE
Rules to Place (+) operator
The outer join symbol (+) cannot be on both sides.
We cannot “ outer join” the same table to more than one other table in a single SELECT statement.
A condition involving an outer join may not use the IN operator or be linked to another condition by the OR operator.
(4) NON-EQUI JOIN
If any other operator instead of equality operator (=) is used to join the tables in the query, it is Non-Equi Join.
SYNTAX: -
SELECT * FROM
WHERE
(5) CARTESIAN JOIN
When the join condition is omitted, the result is the Cartesian join of two or more tables in which all combinations of rows will be displayed. All the rows of the first table are joined to al rows of the second table. This kind of join tends to generate a large number of rows, as it involves no condition.
This join is useful in finding all the possible combination of rows from different tables. This join does not require the tables to have common column between them.
SYNTAX: -
SELECT columnname1, columnname2…
FROM tablename1, tablename2