JOINS
Oracle JOIN
Oracle Joins are used to retrieve data from multiple sources (tables) over related column(s).
Typically one of columns has PRIMARY KEY function and the other has FOREIGN KEY
function. Oracle supports INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and CROSS
JOIN functions. Additionally, if the multiple tables are the same, SELF JOIN should be used
to retrieve data.
INNER JOIN
INNER JOIN combines data from two
tables where there is match on the joining
column(s) in two tables. For its syntax, it
can be written as just ‘JOIN’ (no need to
‘INNER’ keyword)
INNER JOIN example
LEFT OUTER JOIN
LEFT OUTER JOIN returns all of the rows
from the left table with the matching
rows from the right table. If there’s no
matching row found from the right table,
the left join returns null values for the
columns of right table. Note that it can
be written as ‘LEFT JOIN’ also. So no
need to write ‘OUTER’ part.
LEFT OUTER JOIN example
RIGHT OUTER JOIN
The right join is just reversed version of the
left join. It makes a result set that contains
all rows from the right table with the
matching rows from the left table. If there’s
no matching rows from left table, left side
of the table returns null value. ‘OUTER’
keyword is not important for this case also
RIGHT OUTER JOIN example
FULL OUTER JOIN
FULL OUTER JOIN or FULL JOIN returns a result set that
contains all rows from both left and right tables, with
the matching rows from both sides where available. If
there is no match, the missing side will have nulls.
FULL OUTER JOIN example
CROSS JOIN
CROSS JOIN is a deliberate creation of
Cartesian product. There is no join
columns specified, so every possible
combination between tables is presented.
CROSS JOIN example
USING function
USING function is used to alternate ON
function which is work with JOIN functions.
You need to specify the column name to be
used not the whole condition. It is used for
time reduction during writing the queries.
JOIN with multiple tables
JOIN functions can be used for also multiple
tables. For example, You can use JOIN function
for merging 3 different tables. Let’s see it in
example:
AS function in tables
Oracle AS function is used to rename columns,
it can be used to rename tables also. Mostly it
used as short query extensions. There is
difference point between renaming columns
and tables that when rename tables AS
function shouldn’t be written
SELF JOIN
SELF JOIN is a join that combine table with itself. SELF JOIN uses other joins to combine two
or more tables. It is work with table alias to differ name of the same tables, on the other hand
it will not work because of same naming.
SELF JOIN example
SET OPERATORS
SET OPERATORS combine the results of two component queries into a single result. Queries
containing set operators are called compound queries
Operator Output
UNION All distinct rows selected by either query
UNION ALL All rows selected by either query,
duplicates are included
INTERSECT All distinct rows selected by both queries
MINUS All distinct rows selected by first query,
but not the second
UNION
The following statement combines the
result of two queries with the UNION
operator, which eliminates the duplicate
rows
UNION ALL
The following statement combines the
result of two queries with the UNION ALL
operator. With usage of this operator, all of
duplicate values stay remain, not deleted.
INTERSECT
INTERSECT function combines the result of only
rows which are selected by both queries
MINUS
The following statement returns
combination of two queries by MINUS
function which returns only selected
rows by first query, not the second
SUBQUERY
SUBQUERIES are used to writing queries inside of the other queries. As the typical math,
expressions inside of the brackets (subqueries) are always executed firstly, full queries are
executed next.
SUBQUERY example