Subqueries and Their Types

Understanding Subqueries and Their Types

A subquery is – to put it simply – a query within a query. What purpose does a subquery serve? A subquery may be needed when it takes more than a single step to reach the answer. Suppose we need to find all employees who work in the same department as KING. We need to:

Read the Full Article →
Select Rows With Max Value

Select Rows With Max Value

We often need to select rows based on the maximum value of a specific column, such as date. Let’s say we have a table that stores customers’ orders, and we want to list the last order dates and amounts for each customer. Here is a demo data set and query to achieve this. The same […]

Read the Full Article →
SQL WITH Clause - Unreferenced Query

Why the SQL WITH clause is not exactly like a function definition

The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out […]

Read the Full Article →
Thumbnail image for Write Smarter Queries with the SQL WITH Clause

Write Smarter Queries with the SQL WITH Clause

The WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example.

Read the Full Article →
ORA-06592: CASE_NOT_FOUND Exception

ORA-06592: CASE_NOT_FOUND Exception

CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled. Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.

Read the Full Article →
Simple CASE vs. Searched CASE

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE. Let’s have a closer look to compare them in structure and functionality.

Read the Full Article →
Difference Between DECODE and CASE

The Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form: if A = n1 then A1 else if A = n2 then A2 else X Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function. Everything DECODE […]

Read the Full Article →
Selecting ODD or EVEN rows from a table

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table? Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query. Once the order is specified, then the […]

Read the Full Article →
LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy. Consider the hierarchy of employees in SCOTT’s EMP table, shown in tree structure like below:

Read the Full Article →
Hierarchical Queries in Oracle

Hierarchical Queries: A QuickStart Guide

A hierarchical query is one that works on data with a "tree" relationship. An Example of Hierarchical Data The employee-manager relationship in SCOTT’s famous EMP table. Each employee record has a manager’s id associated with it. In effect, there is a "tree" of data relationships At each level, I can get the employee-manager relationship as […]

Read the Full Article →