Tutorial by Examples

A CROSS JOIN performs a join between two tables that does not use an explicit join clause and results in the Cartesian product of two tables. A Cartesian product means each row of one table is combined with each row of the second table in the join. For example, if TABLEA has 20 rows and TABLEB has 2...

An INNER JOIN is a JOIN operation that allows you to specify an explicit join clause. Syntax TableExpression [ INNER ] JOIN TableExpression { ON booleanExpression | USING clause } You can specify the join clause by specifying ON with a boolean expression. The scope of expressions in the ON c...

A LEFT OUTER JOIN performs a join between two tables that requires an explicit join clause but does not exclude unmatched rows from the first table. Example: SELECT ENAME, DNAME, ...

A RIGHT OUTER JOIN performs a join between two tables that requires an explicit join clause but does not exclude unmatched rows from the second table. Example: SELECT ENAME, DNAME, ...

A FULL OUTER JOIN performs a join between two tables that requires an explicit join clause but does not exclude unmatched rows in either table. In other words, it returns all the rows in each table. Example: SELECT * ...

An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side. SELECT * FROM employees WHERE department_id NOT IN (SELECT department_id F...

A semijoin query can be used, for example, to find all departments with at least one employee whose salary exceeds 2500. SELECT * FROM departments WHERE EXISTS (SELECT 1 FROM employees WHERE departments.department_id = employees.department_id AND employees.salary > 25...

The JOIN operation performs a join between two tables, excluding any unmatched rows from the first table. From Oracle 9i forward, the JOIN is equivalent in function to the INNER JOIN. This operation requires an explicit join clause, as opposed to the CROSS JOIN and NATURAL JOIN operators. Example: ...

NATURAL JOIN requires no explitic join condition; it builds one based on all the fields with the same name in the joined tables. create table tab1(id number, descr varchar2(100)); create table tab2(id number, descr varchar2(100)); insert into tab1 values(1, 'one'); insert into tab1 values(2, '...