Category: Oracle

Anti-join is used to make the queries run faster. It is a very powerful SQL construct Oracle offers for faster queries. Anti-join between two tables returns rows from the first table where no matches are found in the second table. It is opposite of a semi-join. An anti-join returns one copy of each row in…

The CROSS JOIN specifies that all rows from first table join with all of the rows of second table. If there are “x” rows in table1 and “y” rows in table2 then the cross join result set have x*y rows. It normally happens when no matching join columns are specified. In simple words you can…

Self Join is a specific type of Join. In Self Join, a table is joined with itself (Unary relationship). A self join simply specifies that each rows of a table is combined with itself and every other row of the table. Syntax Oracle SELF JOIN Example Let’s take a table “customers”. Join this table using…

Oracle Equi join returns the matching column values of the associated tables. It uses a comparison operator in the WHERE clause to refer equality. Syntax Equijoin also can be performed by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality. Syntax Oracle…

An outer join is similar to equijoin but it gets also the non-matched rows from the table. It is categorized in Left Outer Join, Right Outer Join and Full Outer Join by Oracle 9i ANSI/ISO 1999 standard. Left Outer Join Left Outer Join returns all rows from the left (first) table specified in the ON…

Inner Join is the simplest and most common type of join. It is also known as simple join. It returns all rows from multiple tables where the join condition is met. Syntax Image representation of Inner Join Oracle INNER JOIN Example Let’s take an example to perform Inner Join on two tables “Suppliers” and “Order1”.…

Join is a query that is used to combine rows from two or more tables, views, or materialized views. It retrieves data from multiple tables and creates a new table. Join Conditions There may be at least one join condition either in the FROM clause or in the WHERE clause for joining two tables. It…

In Oracle, MINUS operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement has a dataset and the MINUS operator returns all documents from the first dataset and then removes all documents from the second dataset. For example Syntax Parameters 1)…

In Oracle, INTERSECT Operator is used to return the results of 2 or more SELECT statement. It picks the common or intersecting records from compound SELECT queries. Syntax Parameters 1) expression1, expression2, … expression_n: It specifies the columns that you want to retrieve. 2) table1, table2: It specifies the tables that you want to retrieve records from.…

In Oracle, the UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It is different from UNION operator in a way that it does not remove duplicate rows between the various SELECT statements. It returns all of the rows. Each SELECT statement within the UNION ALL must have…