تسلط بر SQL: Join، Window، CTE و ایندکسSQL Mastery: Joins, Windows, CTEs & Indexes

-- PostgreSQL: FROM is optional
SELECT 1 + 1 AS two, current_date AS today;

-- Oracle through 19c: every SELECT needs a FROM; DUAL is the dummy one-row table
SELECT 1 + 1 AS two, TRUNC(SYSDATE) AS today FROM dual;

-- Oracle 23ai onward: FROM is no longer mandatory  -->  SELECT 1 + 1 AS two;

CREATE TABLE department (
    id   INT  PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employee (
    id       INT           PRIMARY KEY,
    name     TEXT          NOT NULL,
    dept_id  INT           REFERENCES department(id),
    salary   NUMERIC(10,2) NOT NULL,
    hired_on DATE          NOT NULL
);

CREATE TABLE department (
    id   NUMBER(10)    PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

CREATE TABLE employee (
    id       NUMBER(10)    PRIMARY KEY,
    name     VARCHAR2(100) NOT NULL,
    dept_id  NUMBER(10)    REFERENCES department(id),
    salary   NUMBER(10,2)  NOT NULL,
    hired_on DATE          NOT NULL
);

INSERT INTO department VALUES (1,'Engineering'),(2,'Sales'),(3,'Legal');

INSERT INTO employee VALUES
  (1,'Ada',   1,    9000, DATE '2020-01-10'),
  (2,'Linus', 1,    9000, DATE '2021-03-01'),
  (3,'Grace', 1,    8000, DATE '2019-07-15'),
  (4,'Sam',   2,    7000, DATE '2022-02-20'),
  (5,'Kai',   2,    7500, DATE '2023-05-05'),
  (6,'Uma',   NULL, 6000, DATE '2023-06-01');  -- no department yet

INSERT INTO department (id, name) VALUES (1,'Engineering');
INSERT INTO department (id, name) VALUES (2,'Sales');
INSERT INTO department (id, name) VALUES (3,'Legal');

-- Through 19c: multi-row inserts use INSERT ALL … SELECT * FROM dual
INSERT ALL
  INTO employee VALUES (1,'Ada',   1,    9000, DATE '2020-01-10')
  INTO employee VALUES (2,'Linus', 1,    9000, DATE '2021-03-01')
  INTO employee VALUES (3,'Grace', 1,    8000, DATE '2019-07-15')
  INTO employee VALUES (4,'Sam',   2,    7000, DATE '2022-02-20')
  INTO employee VALUES (5,'Kai',   2,    7500, DATE '2023-05-05')
  INTO employee VALUES (6,'Uma',   NULL, 6000, DATE '2023-06-01')
SELECT * FROM dual;

COMMIT;   -- without this, no other session sees the data

-- PostgreSQL's non-standard but wonderfully convenient UPDATE … FROM
UPDATE employee e
SET salary = salary * 1.10
FROM department d
WHERE e.dept_id = d.id AND d.name = 'Engineering';

-- Oracle has no UPDATE … FROM. Two idiomatic options:

UPDATE employee e
SET e.salary = e.salary * 1.10
WHERE EXISTS (SELECT 1 FROM department d
              WHERE d.id = e.dept_id AND d.name = 'Engineering');

MERGE INTO employee e
USING (SELECT id FROM department WHERE name = 'Engineering') d
   ON (e.dept_id = d.id)
WHEN MATCHED THEN UPDATE SET e.salary = e.salary * 1.10;
COMMIT;

-- PostgreSQL's native route: ON CONFLICT (the upsert)
INSERT INTO department (id, name) VALUES (3, 'Legal & Compliance')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- Since PostgreSQL 15 the standard MERGE is available too
MERGE INTO department d
USING (SELECT 3 AS id, 'Legal & Compliance' AS name) s
   ON (d.id = s.id)
WHEN MATCHED     THEN UPDATE SET name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

-- Oracle has no ON CONFLICT — MERGE is the equivalent (since 9i)
MERGE INTO department d
USING (SELECT 3 AS id, 'Legal & Compliance' AS name FROM dual) s
   ON (d.id = s.id)
WHEN MATCHED     THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (d.id, d.name) VALUES (s.id, s.name);
COMMIT;

SELECT current_date                    AS today,
       now()                           AS ts_now,
       to_char(hired_on, 'YYYY-MM-DD') AS hired_txt,
       hired_on + INTERVAL '90 days'   AS probation_end
FROM employee
WHERE hired_on >= DATE '2022-01-01'
  AND hired_on <  DATE '2023-01-01';

SELECT TRUNC(SYSDATE)                  AS today,
       SYSTIMESTAMP                    AS ts_now,
       TO_CHAR(hired_on, 'YYYY-MM-DD') AS hired_txt,
       hired_on + 90                   AS probation_end   -- a number means "days"
FROM employee
WHERE hired_on >= DATE '2022-01-01'
  AND hired_on <  DATE '2023-01-01';

SELECT name,
       COALESCE(dept_id::text, 'unassigned')     AS dept,
       CASE WHEN salary > 8000 THEN 'senior'
            WHEN salary > 7000 THEN 'mid'
            ELSE 'junior' END                     AS band,
       NULLIF(salary, 0)                          AS salary_or_null
FROM employee;

SELECT name,
       NVL(TO_CHAR(dept_id), 'unassigned')        AS dept,
       CASE WHEN salary > 8000 THEN 'senior'
            WHEN salary > 7000 THEN 'mid'
            ELSE 'junior' END                     AS band,
       NULLIF(salary, 0)                          AS salary_or_null,
       DECODE(dept_id, 1, 'Eng', 2, 'Sales', 'Other') AS dept_short,  -- legacy style
       NVL2(dept_id, 'assigned', 'free')          AS assignment_state
FROM employee;

CREATE TABLE audit_log (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  msg       TEXT        NOT NULL,
  logged_at timestamptz NOT NULL DEFAULT now()
);

-- In PostgreSQL, RETURNING produces a real result set
INSERT INTO audit_log (msg) VALUES ('raise applied')
RETURNING id, logged_at;

CREATE TABLE audit_log (
  id        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- since 12c
  msg       VARCHAR2(4000) NOT NULL,
  logged_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL
);

-- Oracle has RETURNING too, but only as RETURNING … INTO bind variables
DECLARE
  v_id NUMBER;
BEGIN
  INSERT INTO audit_log (msg) VALUES ('raise applied') RETURNING id INTO v_id;
  COMMIT;
END;
/

-- INNER: employees that HAVE a department (Uma is dropped)
SELECT e.name, d.name AS dept FROM employee e
JOIN department d ON e.dept_id = d.id;

-- LEFT: all employees; Uma appears with dept = NULL
SELECT e.name, d.name AS dept FROM employee e
LEFT JOIN department d ON e.dept_id = d.id;

-- RIGHT: all departments; Legal appears with employee = NULL
SELECT e.name, d.name AS dept FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id;

-- FULL: the union of both
SELECT e.name, d.name AS dept FROM employee e
FULL JOIN department d ON e.dept_id = d.id;

-- Oracle has fully supported ANSI join syntax since 9i and recommends it
SELECT e.name, d.name AS dept FROM employee e
JOIN department d ON e.dept_id = d.id;

SELECT e.name, d.name AS dept FROM employee e
LEFT JOIN department d ON e.dept_id = d.id;

SELECT e.name, d.name AS dept FROM employee e
RIGHT JOIN department d ON e.dept_id = d.id;

SELECT e.name, d.name AS dept FROM employee e
FULL JOIN department d ON e.dept_id = d.id;

-- PostgreSQL has no (+) operator; ANSI is the only form
SELECT e.name, d.name AS dept FROM employee e
LEFT JOIN department d ON e.dept_id = d.id;

SELECT e.name, d.name AS dept
FROM employee e, department d
WHERE e.dept_id = d.id(+);        -- equivalent to LEFT JOIN

SELECT e.name, d.name AS dept
FROM employee e, department d
WHERE e.dept_id(+) = d.id;        -- equivalent to RIGHT JOIN

-- WRONG: behaves like an INNER JOIN, because NULL = 'Sales' is UNKNOWN
SELECT e.name, d.name FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
WHERE d.name = 'Sales';

-- RIGHT: keep LEFT semantics by putting the condition in ON
SELECT e.name, d.name FROM employee e
LEFT JOIN department d ON e.dept_id = d.id AND d.name = 'Sales';

-- Exactly the same trap and the same behavior in Oracle
SELECT e.name, d.name FROM employee e
LEFT JOIN department d ON e.dept_id = d.id
WHERE d.name = 'Sales';           -- silently became INNER

SELECT e.name, d.name FROM employee e
LEFT JOIN department d ON e.dept_id = d.id AND d.name = 'Sales';

-- And in the legacy syntax: every predicate on d needs its own (+)
SELECT e.name, d.name
FROM employee e, department d
WHERE e.dept_id = d.id(+) AND d.name(+) = 'Sales';

-- Departments with no employees
SELECT d.name FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.id);

-- Alternative form: LEFT JOIN + IS NULL (usually the same plan)
SELECT d.name FROM department d
LEFT JOIN employee e ON e.dept_id = d.id
WHERE e.id IS NULL;

SELECT d.name FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee e WHERE e.dept_id = d.id);

SELECT d.name FROM department d
LEFT JOIN employee e ON e.dept_id = d.id
WHERE e.id IS NULL;

-- Oracle has one extra tool: MINUS (the EXCEPT equivalent)
SELECT id FROM department
MINUS
SELECT dept_id FROM employee WHERE dept_id IS NOT NULL;

-- PostgreSQL ships no hints; its philosophy is "fix the optimizer, not the plan"
SET enable_hashjoin = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT e.name, d.name FROM employee e JOIN department d ON d.id = e.dept_id;
RESET enable_hashjoin;
-- (the pg_hint_plan extension adds Oracle-style comment hints if installed)

-- Oracle has a full, documented hint system
SELECT /*+ LEADING(d e) USE_HASH(e) FULL(e) */ e.name, d.name
FROM employee e JOIN department d ON d.id = e.dept_id;

SELECT /*+ INDEX(e ix_emp_dept_sal) */ e.name, e.salary
FROM employee e WHERE e.dept_id = 1;

SELECT d.name,
       COUNT(*)      AS headcount,
       AVG(e.salary) AS avg_salary,
       MAX(e.salary) AS top_salary
FROM employee e JOIN department d ON d.id = e.dept_id
WHERE e.hired_on >= DATE '2021-01-01'   -- filter rows first
GROUP BY d.name
HAVING AVG(e.salary) > 7500;            -- then filter groups

SELECT d.name,
       COUNT(*)      AS headcount,
       AVG(e.salary) AS avg_salary,
       MAX(e.salary) AS top_salary
FROM employee e JOIN department d ON d.id = e.dept_id
WHERE e.hired_on >= DATE '2021-01-01'
GROUP BY d.name
HAVING AVG(e.salary) > 7500;

SELECT d.name AS dept,
       COUNT(*) AS headcount,
       STRING_AGG(e.name, ', ' ORDER BY e.salary DESC) AS members
FROM employee e JOIN department d ON d.id = e.dept_id
GROUP BY d.name;

SELECT d.name AS dept,
       COUNT(*) AS headcount,
       -- WITHIN GROUP ordering is mandatory; ON OVERFLOW prevents ORA-01489
       LISTAGG(e.name, ', ' ON OVERFLOW TRUNCATE '…' WITH COUNT)
         WITHIN GROUP (ORDER BY e.salary DESC) AS members
FROM employee e JOIN department d ON d.id = e.dept_id
GROUP BY d.name;

-- PostgreSQL has the standard FILTER clause; the most readable form there is
SELECT dept_id,
       COUNT(*)                                                 AS total,
       COUNT(*) FILTER (WHERE salary > 8000)                    AS high_earners,
       SUM(salary) FILTER (WHERE hired_on >= DATE '2022-01-01') AS recent_payroll
FROM employee GROUP BY dept_id;

-- Oracle has no FILTER clause — CASE inside the aggregate is the exact equivalent
SELECT dept_id,
       COUNT(*)                                              AS total,
       COUNT(CASE WHEN salary > 8000 THEN 1 END)             AS high_earners,
       SUM(CASE WHEN hired_on >= DATE '2022-01-01'
                THEN salary END)                             AS recent_payroll
FROM employee GROUP BY dept_id;

WITH dept_stats AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employee GROUP BY dept_id
)
SELECT e.name, e.salary, s.avg_sal
FROM employee e
JOIN dept_stats s ON s.dept_id = e.dept_id
WHERE e.salary > s.avg_sal;   -- earns above their department average

-- Oracle calls this "subquery factoring"; the syntax is identical
WITH dept_stats AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employee GROUP BY dept_id
)
SELECT e.name, e.salary, s.avg_sal
FROM employee e
JOIN dept_stats s ON s.dept_id = e.dept_id
WHERE e.salary > s.avg_sal;

WITH recent AS MATERIALIZED (       -- force the fence
  SELECT * FROM employee WHERE hired_on >= DATE '2022-01-01'
)
SELECT * FROM recent WHERE dept_id = 2;

-- And the reverse: NOT MATERIALIZED forces inlining even for multi-reference CTEs
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM employee WHERE hired_on >= DATE '2022-01-01'
)
SELECT * FROM recent WHERE dept_id = 2;

-- Oracle has no MATERIALIZED keyword; two hints do the same job
WITH recent AS (
  SELECT /*+ MATERIALIZE */ * FROM employee WHERE hired_on >= DATE '2022-01-01'
)
SELECT * FROM recent WHERE dept_id = 2;

WITH recent AS (
  SELECT /*+ INLINE */ * FROM employee WHERE hired_on >= DATE '2022-01-01'
)
SELECT * FROM recent WHERE dept_id = 2;

CREATE TABLE emp_tree (id INT PRIMARY KEY, name TEXT, manager_id INT);
INSERT INTO emp_tree VALUES
  (1,'CEO',NULL),(2,'CTO',1),(3,'VP Eng',2),(4,'Dev A',3),(5,'Dev B',3);

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM emp_tree WHERE id = 4                 -- anchor: start at Dev A
  UNION ALL
    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM emp_tree e
    JOIN chain c ON e.id = c.manager_id        -- climb toward the CEO
)
SELECT * FROM chain;   -- Dev A -> VP Eng -> CTO -> CEO

CREATE TABLE emp_tree (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50), manager_id NUMBER(10));
INSERT ALL
  INTO emp_tree VALUES (1,'CEO',NULL)  INTO emp_tree VALUES (2,'CTO',1)
  INTO emp_tree VALUES (3,'VP Eng',2)  INTO emp_tree VALUES (4,'Dev A',3)
  INTO emp_tree VALUES (5,'Dev B',3)
SELECT * FROM dual;
COMMIT;

-- Oracle has no RECURSIVE keyword, but the column alias list is mandatory
WITH chain (id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 1 FROM emp_tree WHERE id = 4
  UNION ALL
    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM emp_tree e JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

-- PostgreSQL has no CONNECT BY; you build the pseudo-columns yourself
WITH RECURSIVE t AS (
    SELECT id, name, manager_id,
           1              AS lvl,        -- the LEVEL equivalent
           name           AS root_name,  -- the CONNECT_BY_ROOT equivalent
           ' -> ' || name AS path        -- the SYS_CONNECT_BY_PATH equivalent
    FROM emp_tree WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id,
           t.lvl + 1, t.root_name, t.path || ' -> ' || e.name
    FROM emp_tree e JOIN t ON e.manager_id = t.id
)
SELECT lvl, LPAD(' ', (lvl-1)*2) || name AS org, path FROM t ORDER BY path;

-- The legacy Oracle syntax: far more compact, with ready-made pseudo-columns
SELECT LEVEL                             AS lvl,
       LPAD(' ', (LEVEL-1)*2) || name    AS org,
       CONNECT_BY_ROOT name              AS root_name,
       SYS_CONNECT_BY_PATH(name, ' -> ') AS path,
       CONNECT_BY_ISLEAF                 AS is_leaf
FROM emp_tree
START WITH manager_id IS NULL            -- the anchor
CONNECT BY NOCYCLE PRIOR id = manager_id -- the recursive step; PRIOR = parent row
ORDER SIBLINGS BY name;

SELECT name, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense,
  AVG(salary)  OVER (PARTITION BY dept_id)                      AS dept_avg,
  ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept_id), 1) AS pct
FROM employee;

SELECT name, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense,
  AVG(salary)  OVER (PARTITION BY dept_id)                      AS dept_avg,
  -- RATIO_TO_REPORT is Oracle-only and gives the same ratio directly
  ROUND(100 * RATIO_TO_REPORT(salary) OVER (PARTITION BY dept_id), 1) AS pct
FROM employee;

SELECT name, hired_on, salary,
  LAG(salary)  OVER (ORDER BY hired_on)         AS prev_salary,
  LEAD(salary) OVER (ORDER BY hired_on)         AS next_salary,
  salary - LAG(salary) OVER (ORDER BY hired_on) AS delta
FROM employee;
-- Note: PostgreSQL 17 has no IGNORE NULLS clause; behavior is always RESPECT NULLS.

SELECT name, hired_on, salary,
  LAG(salary)  OVER (ORDER BY hired_on)         AS prev_salary,
  LEAD(salary) OVER (ORDER BY hired_on)         AS next_salary,
  salary - LAG(salary) OVER (ORDER BY hired_on) AS delta,
  -- Oracle has had IGNORE NULLS from the start: fetch the last non-NULL value
  LAG(salary IGNORE NULLS) OVER (ORDER BY hired_on) AS prev_non_null
FROM employee;

SELECT name, hired_on, salary,
  SUM(salary) OVER (ORDER BY hired_on
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(salary) OVER (ORDER BY hired_on
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS moving_avg_3,
  SUM(salary) OVER (ORDER BY salary)                                  AS range_default
FROM employee;

SELECT name, hired_on, salary,
  SUM(salary) OVER (ORDER BY hired_on
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(salary) OVER (ORDER BY hired_on
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)         AS moving_avg_3,
  SUM(salary) OVER (ORDER BY salary)                                  AS range_default
FROM employee;

-- PostgreSQL has no KEEP clause. Two common equivalents:
SELECT dept_id,
       MAX(salary)                                    AS top_salary,
       (ARRAY_AGG(name ORDER BY salary DESC, id))[1]  AS top_earner
FROM employee GROUP BY dept_id;

-- DISTINCT ON — PostgreSQL-only, for "one top row per group"
SELECT DISTINCT ON (dept_id) dept_id, name AS top_earner, salary
FROM employee ORDER BY dept_id, salary DESC, id;

-- Oracle: FIRST/LAST with KEEP (DENSE_RANK …) — one pass, no subquery
SELECT dept_id,
       MAX(salary)                                                AS top_salary,
       MAX(name) KEEP (DENSE_RANK FIRST ORDER BY salary DESC, id) AS top_earner,
       MAX(name) KEEP (DENSE_RANK LAST  ORDER BY salary DESC, id) AS bottom_earner
FROM employee GROUP BY dept_id;

SELECT id FROM employee WHERE dept_id = 1
EXCEPT
SELECT id FROM employee WHERE salary < 8500;   -- Eng employees earning >= 8500

-- PostgreSQL also has the ALL variants (which preserve duplicate counts)
SELECT dept_id FROM employee EXCEPT ALL SELECT id FROM department;

-- The historical name of this operator in Oracle is MINUS
SELECT id FROM employee WHERE dept_id = 1
MINUS
SELECT id FROM employee WHERE salary < 8500;

-- Since Oracle 21c, EXCEPT was added as a synonym for MINUS,
-- along with MINUS ALL / EXCEPT ALL / INTERSECT ALL
SELECT id FROM employee WHERE dept_id = 1
EXCEPT
SELECT id FROM employee WHERE salary < 8500;

SELECT id, name, salary FROM employee
ORDER BY salary DESC, id
LIMIT 10 OFFSET 20;

-- The ANSI form, understood by PostgreSQL since 8.4 (and more portable)
SELECT id, name, salary FROM employee
ORDER BY salary DESC, id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- Oracle has no LIMIT at all. Since 12c, the standard form:
SELECT id, name, salary FROM employee
ORDER BY salary DESC, id
OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

-- And if the last row's ties must come along (both engines support this):
SELECT id, name, salary FROM employee
ORDER BY salary DESC
FETCH FIRST 3 ROWS WITH TIES;

-- PostgreSQL has no ROWNUM-like pseudo-column; the logical equivalent just works
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 3;

-- And if you want a per-page row number:
SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS rn FROM employee t
) x WHERE rn BETWEEN 21 AND 30;

-- WRONG: ROWNUM is assigned before ORDER BY
-- -> three arbitrary rows are taken, and only those three get sorted
SELECT name, salary FROM employee WHERE ROWNUM <= 3 ORDER BY salary DESC;

-- The classic correct form (pre-12c): sort in an inline view, apply ROWNUM outside
SELECT * FROM (SELECT name, salary FROM employee ORDER BY salary DESC)
WHERE ROWNUM <= 3;

-- The modern correct form (12c+):
SELECT name, salary FROM employee ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY;

-- PostgreSQL supports multi-column row-value comparison directly
SELECT id, name, salary FROM employee
WHERE (salary, id) < (7500, 5)          -- the previous page's key, from the client
ORDER BY salary DESC, id DESC
FETCH FIRST 10 ROWS ONLY;

-- Oracle supports row-value comparison only for = and IN, not for < and >
-- so you must expand the predicate by hand:
SELECT id, name, salary FROM employee
WHERE salary < 7500
   OR (salary = 7500 AND id < 5)
ORDER BY salary DESC, id DESC
FETCH FIRST 10 ROWS ONLY;

-- PostgreSQL: the empty string is a real zero-length value, not NULL
SELECT CASE WHEN '' IS NULL THEN 'empty string IS NULL'
            ELSE 'empty string is NOT null' END AS truth,
       LENGTH('') AS len;
-- prints: empty string is NOT null | 0

-- Oracle: the empty string and NULL are the same thing (for VARCHAR2)
SELECT CASE WHEN '' IS NULL THEN 'empty string IS NULL'
            ELSE 'empty string is NOT null' END AS truth,
       LENGTH('') AS len
FROM dual;
-- prints: empty string IS NULL | (NULL)   -- even LENGTH('') is NULL, not zero

-- PostgreSQL has the standard IS [NOT] DISTINCT FROM: it never yields UNKNOWN
SELECT a.name, b.name FROM employee a JOIN employee b
  ON a.dept_id IS NOT DISTINCT FROM b.dept_id   -- two NULLs count as "equal"
 AND a.id < b.id;

SELECT name, dept_id FROM employee ORDER BY dept_id ASC NULLS FIRST;

-- Oracle has no IS [NOT] DISTINCT FROM. Two idiomatic equivalents:
-- 1) DECODE treats two NULLs as equal (a documented DECODE special case)
SELECT a.name, b.name FROM employee a JOIN employee b
  ON DECODE(a.dept_id, b.dept_id, 0, 1) = 0 AND a.id < b.id;

-- 2) The explicit, portable form
SELECT a.name, b.name FROM employee a JOIN employee b
  ON (a.dept_id = b.dept_id OR (a.dept_id IS NULL AND b.dept_id IS NULL))
 AND a.id < b.id;

SELECT name, dept_id FROM employee ORDER BY dept_id ASC NULLS FIRST;

CREATE INDEX ix_emp_dept_sal ON employee (dept_id, salary);

SELECT * FROM employee WHERE dept_id = 1 AND salary > 8000;    -- fully served
SELECT * FROM employee WHERE dept_id = 1 ORDER BY salary DESC; -- free ordering
SELECT * FROM employee WHERE salary > 8000;                    -- cannot be served

CREATE INDEX ix_emp_dept_sal ON employee (dept_id, salary);

SELECT * FROM employee WHERE dept_id = 1 AND salary > 8000;
SELECT * FROM employee WHERE dept_id = 1 ORDER BY salary DESC;

-- Here Oracle may pick INDEX FAST FULL SCAN (reading the whole index like a
-- narrow table) — better than TABLE ACCESS FULL, still far worse than a real
-- INDEX RANGE SCAN
SELECT * FROM employee WHERE salary > 8000;

-- Since PostgreSQL 11: non-key payload columns ride along via INCLUDE
-- (they are not part of the sort key, just carried on the leaf pages)
CREATE INDEX ix_cover ON employee (dept_id, salary) INCLUDE (name);

EXPLAIN (ANALYZE, BUFFERS)
SELECT dept_id, salary, name FROM employee WHERE dept_id = 1;
-- look for "Index Only Scan" and "Heap Fetches: 0" in the plan

-- Oracle has no INCLUDE clause: the extra column simply joins the key
CREATE INDEX ix_cover ON employee (dept_id, salary, name);

SELECT /*+ GATHER_PLAN_STATISTICS */ dept_id, salary, name
FROM employee WHERE dept_id = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));
-- the sign of covering: no TABLE ACCESS BY INDEX ROWID step in the plan

-- PostgreSQL has native partial indexes: only matching rows are stored
CREATE INDEX ix_active_hi ON employee (salary)
WHERE hired_on >= DATE '2022-01-01';

SELECT * FROM employee
WHERE hired_on >= DATE '2022-01-01' AND salary > 7000;

-- Oracle has no such thing. The standard trick: a function-based index that
-- returns NULL for unwanted rows — and since a B-tree does not store all-NULL
-- keys, the index effectively becomes partial.
CREATE INDEX ix_active_hi ON employee
  (CASE WHEN hired_on >= DATE '2022-01-01' THEN salary END);

-- The query must use the *identical* expression to hit the index
SELECT * FROM employee
WHERE (CASE WHEN hired_on >= DATE '2022-01-01' THEN salary END) > 7000;

-- The cure: an expression index
CREATE INDEX ix_emp_lname ON employee (lower(name));
SELECT * FROM employee WHERE lower(name) = 'ada';

-- For substring search ('%ada%') a B-tree is useless; you need trigrams
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ix_emp_name_trgm ON employee USING gin (name gin_trgm_ops);

-- The same cure: a function-based index (since 8i)
CREATE INDEX ix_emp_lname ON employee (LOWER(name));
SELECT * FROM employee WHERE LOWER(name) = 'ada';

-- For substring search you need Oracle Text:
-- CREATE INDEX ix_name_txt ON employee(name) INDEXTYPE IS CTXSYS.CONTEXT;

-- Oracle's golden experiment tool: make the index invisible instead of dropping it
ALTER INDEX ix_emp_lname INVISIBLE;   -- optimizer ignores it, but it stays maintained
ALTER INDEX ix_emp_lname VISIBLE;     -- instant rollback

ANALYZE employee;
ALTER TABLE employee ALTER COLUMN dept_id SET STATISTICS 500;

-- Multi-column statistics for correlated columns
CREATE STATISTICS st_emp (dependencies, ndistinct) ON dept_id, salary FROM employee;
ANALYZE employee;

SELECT relname, n_live_tup, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'employee';

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'EMPLOYEE',
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',   -- automatic histograms
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE);                         -- include indexes
END;
/

-- Oracle's answer to CREATE STATISTICS: extended statistics
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER,'EMPLOYEE','(dept_id, salary)') FROM dual;

SELECT table_name, num_rows, last_analyzed FROM user_tables
WHERE table_name = 'EMPLOYEE';

-- EXPLAIN alone = the estimated plan, without running anything
EXPLAIN SELECT e.name, d.name FROM employee e
JOIN department d ON d.id = e.dept_id WHERE e.salary > 8000;

-- EXPLAIN ANALYZE = actually runs it and reports real timings and row counts
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT e.name, d.name FROM employee e
JOIN department d ON d.id = e.dept_id WHERE e.salary > 8000;

-- (a) estimated plan only, no execution
EXPLAIN PLAN FOR
SELECT e.name, d.name FROM employee e
JOIN department d ON d.id = e.dept_id WHERE e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));

-- (b) the real EXPLAIN ANALYZE equivalent: run with row-source stats, then read them
SELECT /*+ GATHER_PLAN_STATISTICS */ e.name, d.name FROM employee e
JOIN department d ON d.id = e.dept_id WHERE e.salary > 8000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

-- (c) the quick way in SQL*Plus / SQLcl
SET AUTOTRACE ON

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Oracle's native equivalent: the V$SQL view (and AWR / ASH for history)
SELECT sql_id, executions,
       ROUND(elapsed_time/1e6, 1)                        AS total_sec,
       ROUND(elapsed_time/GREATEST(executions,1)/1e3, 2) AS mean_ms,
       buffer_gets, rows_processed, sql_text
FROM v$sql
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- Window approach (handles ties via DENSE_RANK)
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employee
)
SELECT DISTINCT salary FROM ranked WHERE r = 2;

-- Classic subquery: always exactly one row, graceful when there is no second value
SELECT MAX(salary) AS second_highest FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

-- The row-limiting form (which has different NULL behavior!)
SELECT DISTINCT salary FROM employee
ORDER BY salary DESC OFFSET 1 ROWS FETCH FIRST 1 ROW ONLY;

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employee
)
SELECT DISTINCT salary FROM ranked WHERE r = 2;

SELECT MAX(salary) AS second_highest FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

SELECT DISTINCT salary FROM employee
ORDER BY salary DESC OFFSET 1 ROWS FETCH FIRST 1 ROW ONLY;

-- The canonical, portable pattern
SELECT dept_id, name, salary FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM employee e
) t WHERE rn <= 2;   -- top 2 per department

-- PostgreSQL's shortcut for top-1
SELECT DISTINCT ON (dept_id) dept_id, name, salary
FROM employee ORDER BY dept_id, salary DESC, id;

SELECT dept_id, name, salary FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM employee e
) t WHERE rn <= 2;

-- Oracle's shortcut for top-1 (one pass, no subquery)
SELECT dept_id,
       MAX(name)   KEEP (DENSE_RANK FIRST ORDER BY salary DESC, id) AS name,
       MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY salary DESC, id) AS salary
FROM employee GROUP BY dept_id;

CREATE TABLE logins (user_id INT, day INT);
INSERT INTO logins VALUES (1,1),(1,2),(1,3),(1,6),(1,7),(1,10);

WITH grp AS (
  SELECT day, day - ROW_NUMBER() OVER (ORDER BY day) AS island_key
  FROM logins WHERE user_id = 1
)
SELECT MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS len
FROM grp GROUP BY island_key ORDER BY start_day;
-- islands: [1-3], [6-7], [10-10]; the gaps are 4-5 and 8-9

CREATE TABLE logins (user_id NUMBER(10), day NUMBER(10));
INSERT ALL
  INTO logins VALUES (1,1) INTO logins VALUES (1,2) INTO logins VALUES (1,3)
  INTO logins VALUES (1,6) INTO logins VALUES (1,7) INTO logins VALUES (1,10)
SELECT * FROM dual;
COMMIT;

WITH grp AS (
  SELECT day, day - ROW_NUMBER() OVER (ORDER BY day) AS island_key
  FROM logins WHERE user_id = 1
)
SELECT MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS len
FROM grp GROUP BY island_key ORDER BY start_day;

-- PostgreSQL has no MATCH_RECOGNIZE; the island_key trick is the portable answer
WITH grp AS (
  SELECT user_id, day,
         day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) AS island_key
  FROM logins
)
SELECT user_id, MIN(day) AS start_day, MAX(day) AS end_day, COUNT(*) AS len
FROM grp GROUP BY user_id, island_key ORDER BY user_id, start_day;

-- MATCH_RECOGNIZE: describe the pattern directly, no arithmetic trick needed
SELECT * FROM logins
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER BY day
  MEASURES FIRST(day) AS start_day, LAST(day) AS end_day, COUNT(*) AS len
  ONE ROW PER MATCH
  PATTERN (a b*)
  DEFINE b AS b.day = PREV(b.day) + 1
);