Table of Contents DDL DML NULL WITH Inline view Using a WITH clause Joins

If field1 can be null, DO NOT filter without IS (NOT) NULL condition
DO NOT DO THIS!! AND field <> 1
DO THIS field1 IS NOT NULL AND filed1 <> 1

Using vendor-alternatives, like NVL() in Oracle may cause problems because of special values
eg: NVL(field1, 0, field1) <> 1
It may work now, but as soon as developer will use 0 in a row, il will break..

Source

Difference with subqueries:

• readibility;
• performance.

Also, avoid switching to procedural (than can imply performance loss) because of query complexity without it.

Example

For each employee:

• manager name;
• how many other people are in their department ?

SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc1
         ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN (SELECT deptno, COUNT(*) AS dept_count
             FROM   emp
             GROUP BY deptno) dc2
         ON m.deptno = dc2.deptno;

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e
       JOIN dept_count dc1 ON e.deptno = dc1.deptno
       JOIN emp m ON e.mgr = m.empno
       JOIN dept_count dc2 ON m.deptno = dc2.deptno;

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.

If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient

Can use multiple subqueries using renaming

WITH 
   src_done  AS (SELECT id FROM source WHERE status = 'DONE'),
   src_valid AS (SELECT id FROM source WHERE status <> 'CANCEL' AND cancel_date < SYSDATE)
SELECT
(...)
FROM data
WHERE data.id IN (src_done.id, src_valid.id) 

Can use it including INSERT INTO

INSERT INTO <TARGET TABLE>
WITH 
   source AS (.. FROM <SOURCE>..)
SELECT
(..)
FROM 
   <INTERMEDIATE> INNER JOIN source (..)

These are equivalent, but ON doesn't work if:

• column names are not the same in the two tables;
• you want to use the joining column.

select e.ename, d.dname
from emp e join dept d using (deptno);

select e.ename, d.dname
from emp e join dept d on d.deptno = e.deptno;