Basic Querying with SELECT

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → OFFSET/FETCH

7.1 SELECT Syntax

SELECT   [DISTINCT | ALL] column_list
FROM     table_source
WHERE    condition
GROUP BY expression
HAVING   condition
ORDER BY expression [ASC|DESC] [NULLS FIRST|LAST]
OFFSET n ROWS FETCH {FIRST|NEXT} m {ROWS|PERCENT} {ONLY|WITH TIES};

7.2 Logical Processing Order

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → OFFSET/FETCH

Before 23ai you can't use a SELECT alias in WHERE or GROUP BY. 23ai allows GROUP BY alias/position (GROUP BY 1 with group_by_position_enabled).

7.3 Basics

SELECT * FROM employees;
SELECT first_name, last_name, salary * 12 AS annual_salary FROM hr.employees;
SELECT first_name || ' ' || last_name AS "Full Name" FROM hr.employees;   -- concatenation
SELECT DISTINCT department_id FROM hr.employees;
SELECT 'Hello' FROM dual;
SELECT SYSDATE;                                   -- 23ai: FROM DUAL is optional
SELECT q'[It's easy]' FROM dual;                  -- alternative quoting

7.4 Filtering

SELECT * FROM employees WHERE salary > 10000;
SELECT * FROM employees WHERE department_id IN (10, 20, 30);
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;
SELECT * FROM employees WHERE last_name LIKE 'S%';
SELECT * FROM employees WHERE job_id LIKE 'SA\_%' ESCAPE '\';
SELECT * FROM employees WHERE commission_pct IS NULL;
SELECT * FROM employees WHERE hire_date >= DATE '2020-01-01';      -- ANSI date literal
SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND salary > 5000;

7.5 NULL Handling

Any arithmetic or comparison with NULL yields NULL/UNKNOWN. '' IS NULL is TRUE in Oracle.

NOT IN with a NULL in the list returns no rows.

7.6 Sorting

SELECT last_name, salary FROM employees ORDER BY salary DESC NULLS LAST, last_name;
SELECT last_name, salary * 12 annual FROM employees ORDER BY annual;
SELECT last_name FROM employees ORDER BY 1;

7.7 Row Limiting

-- 12c+
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;
SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 PERCENT ROWS WITH TIES;
SELECT * FROM employees ORDER BY employee_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Classic ROWNUM (assigned before ORDER BY!)
SELECT * FROM (SELECT * FROM employees ORDER BY salary DESC) WHERE ROWNUM <= 5;

7.8 Substitution Variables (SQL*Plus / SQLcl)

SELECT * FROM employees WHERE department_id = &dept_id;
SELECT * FROM employees WHERE last_name = '&&name';   -- && reuses the value
ACCEPT dept PROMPT 'Enter department: '

7.9 CASE and DECODE

SELECT last_name,
       CASE WHEN salary < 5000 THEN 'Low'
            WHEN salary < 15000 THEN 'Medium'
            ELSE 'High' END AS band,
       DECODE(department_id, 10, 'Admin', 20, 'Marketing', 'Other') AS dept_name
FROM employees;