SQL in Depth

Before writing a single line of SQL, understand what it is operating on. A relation (Codd's term) is simply a set of tuples, all sharing the same schema — a list of named, typed attributes. In SQL this is called a table. The key insight is that a relation is a set, which means:

1. There is no inherent ordering to the rows.
2. There are no duplicate rows (in pure relational theory; SQL relaxes this with multisets/bags).
3. Every tuple is identified by its attribute values, not by a position.

The declarative mindset means expressing a query as a description of the desired output relation, not as a procedure. Compare:

Imperative (Python): "Iterate over every order, check if the customer is from Germany, keep those, sort by amount."

Declarative (SQL): "The set of orders where the customer is from Germany, ordered by amount."

The engine then uses relational algebra — a formal system of set operations — to compute the result. The five fundamental operations are: Selection (σ, filter rows), Projection (π, keep certain columns), Cartesian Product (×, all combinations of two tables), Union (∪), and Difference (−). Joins, aggregations, and every other SQL construct desugar into these primitives.

Why does this matter? Because it tells you how to reason about correctness: a query is correct if it describes the right set of tuples, regardless of how the engine executes it. It also tells you why the engine can reorder, merge, or skip operations — because set operations obey algebraic laws (commutativity, associativity, distributivity) that allow equivalent reformulations.

-- The mental model: each clause transforms a relation
-- FROM   →  define the input relation(s)
-- WHERE  →  apply selection (σ)
-- SELECT →  apply projection (π) and expressions
-- GROUP BY → partition into groups
-- HAVING →  filter groups
-- ORDER BY → impose an ordering on the result bag
-- LIMIT  →  truncate

-- Logical evaluation order (NOT textual order):
-- FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

SELECT customer_id, SUM(amount) AS total
FROM   orders
WHERE  status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC
LIMIT  10;

Data Definition Language (DDL) is the set of SQL statements that create, alter, and drop database objects — tables, indexes, views, sequences, types. DDL is how you encode your data model into the database.

CREATE TABLE specifies column names, types, and constraints. SQL types include INTEGER, BIGINT, NUMERIC(p,s) (exact decimal with precision p and scale s), FLOAT/DOUBLE PRECISION (IEEE 754), VARCHAR(n) (variable-length string up to n chars), TEXT, BOOLEAN, DATE, TIMESTAMP, JSONB (PostgreSQL's binary JSON), and more.

Constraints are predicates the database enforces on every write:

• NOT NULL: the column may never be NULL.
• UNIQUE: no two rows may share the same value (NULLs are typically exempt).
• PRIMARY KEY: the combination of NOT NULL + UNIQUE that identifies each row. A table should have exactly one.
• FOREIGN KEY: a value in this column must exist as a primary key in another table. This enforces referential integrity — the relational guarantee that references are never dangling.
• CHECK: an arbitrary boolean expression that must hold (e.g., age >= 0).
• DEFAULT: a value to use if none is supplied on INSERT.

ALTER TABLE adds/drops/renames columns and constraints. DROP TABLE deletes the table and all its data. These are DDL operations and in most databases are auto-committed — there is no rollback, so use them carefully in production.

A central design decision is normalization: organizing tables to reduce redundancy and update anomalies. In First Normal Form (1NF), every cell is atomic (no lists). In 2NF, every non-key attribute depends on the whole primary key. In 3NF, non-key attributes depend only on the key (no transitive dependencies). In practice, most schemas aim for 3NF or BCNF, then selectively denormalize for query performance.

CREATE TABLE customers (
  customer_id  SERIAL        PRIMARY KEY,
  email        VARCHAR(255)  NOT NULL UNIQUE,
  country_code CHAR(2)       NOT NULL,
  created_at   TIMESTAMP     NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  order_id    SERIAL          PRIMARY KEY,
  customer_id INTEGER         NOT NULL
                              REFERENCES customers(customer_id)
                              ON DELETE RESTRICT,
  amount      NUMERIC(12, 2)  NOT NULL CHECK (amount > 0),
  status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
  placed_at   TIMESTAMP       NOT NULL DEFAULT NOW()
);

-- Add an index for a common query pattern
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Add a column later
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

Data Manipulation Language (DML) covers the four CRUD operations: INSERT, SELECT, UPDATE, DELETE (SELECT is sometimes classified separately as DQL — Data Query Language).

INSERT adds new rows. You can insert a single row, multiple rows in one statement (much faster than looping), or the result of a SELECT (INSERT INTO ... SELECT ...).

UPDATE modifies existing rows matching a WHERE clause. Be very careful: an UPDATE without a WHERE clause updates every row. Always write and test the WHERE clause as a SELECT first.

DELETE removes rows matching a WHERE clause. Similarly, DELETE without WHERE removes all rows (though TRUNCATE is faster for that use case — it deallocates pages rather than deleting row by row).

NULL semantics are crucial and often surprising. NULL means "unknown" or "inapplicable", not zero or empty string. SQL uses three-valued logic: a predicate can be TRUE, FALSE, or UNKNOWN (which is what you get when one operand is NULL). The key rules:

• NULL = NULL is UNKNOWN (not TRUE). Use IS NULL / IS NOT NULL.
• Any arithmetic with NULL returns NULL.
• In WHERE and HAVING, only rows where the predicate is TRUE are kept — UNKNOWN rows are excluded.
• In CHECK constraints, UNKNOWN is treated as TRUE (a constraint passes if the condition is UNKNOWN).
• Aggregate functions (SUM, AVG, COUNT(col)) ignore NULLs. COUNT(*) counts all rows including those with NULLs.

Understanding NULL semantics is one of the most important — and most commonly botched — aspects of SQL.

-- INSERT: single row
INSERT INTO customers (email, country_code)
VALUES ('alice@example.com', 'US');

-- INSERT: multiple rows in one statement
INSERT INTO orders (customer_id, amount, status)
VALUES
  (1, 299.99, 'completed'),
  (1, 49.00,  'completed'),
  (2, 1200.00, 'pending');

-- UPDATE: always test the WHERE as a SELECT first!
UPDATE orders
SET    status = 'cancelled'
WHERE  placed_at < NOW() - INTERVAL '30 days'
  AND  status = 'pending';

-- DELETE
DELETE FROM orders WHERE order_id = 42;

-- NULL trap: this returns NO rows even if some names are NULL
-- because (name != 'Alice') is UNKNOWN when name IS NULL
SELECT * FROM users WHERE name != 'Alice';

-- Correct: explicitly handle NULLs
SELECT * FROM users
WHERE  name != 'Alice' OR name IS NULL;

A join combines rows from two (or more) tables based on a related column. Joins are the heart of relational query writing, and understanding them precisely requires thinking in terms of sets.

Start with the Cartesian product (CROSS JOIN): every row from table A paired with every row from table B. If A has m rows and B has n rows, the result has m × n rows. Almost no useful query wants this directly — but every join starts as a Cartesian product and then filters.

INNER JOIN (usually just JOIN): keep only pairs where the join condition holds. This is σ(condition)(A × B). If a row in A has no matching row in B, it is dropped.

LEFT OUTER JOIN: keep all rows from A. For rows in A with no match in B, fill B's columns with NULL. Use this when you need "all customers, even those with no orders".

RIGHT OUTER JOIN: mirror of LEFT. Rarely used in practice — you can always rephrase it as a LEFT JOIN with tables swapped.

FULL OUTER JOIN: keep all rows from both tables, filling with NULLs where there is no match on either side.

SELF JOIN: join a table to itself, necessary for hierarchical data (e.g., employees and their managers in the same table).

NATURAL JOIN and JOIN USING: convenience syntax that matches on identically-named columns. Natural joins are dangerous in practice because they silently change semantics when column names change — prefer explicit ON conditions.

A critical intuition: after an INNER JOIN, you can move predicates between ON and WHERE freely. But with OUTER JOINs, the location matters profoundly: a condition in ON is applied before the outer join (NULLs are still preserved), while a condition in WHERE is applied after (and eliminates the NULL-padded rows, effectively turning an OUTER JOIN into an INNER JOIN). This is a frequent source of subtle bugs.

-- INNER JOIN: only customers who have placed orders
SELECT c.email, o.amount
FROM   customers c
JOIN   orders o ON o.customer_id = c.customer_id;

-- LEFT JOIN: all customers, even those with no orders
SELECT c.email, o.amount   -- o.amount is NULL for customers with no orders
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

-- Find customers with NO orders (anti-join pattern)
SELECT c.email
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE  o.order_id IS NULL;

-- Self-join: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM   employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

-- OUTER JOIN WHERE-clause trap:
-- This LOOKS like a LEFT JOIN but is actually an INNER JOIN
-- because the WHERE filters out NULLs
SELECT c.email, o.amount
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE  o.status = 'completed';  -- eliminates NULL rows!

-- Correct: move the filter to ON
SELECT c.email, o.amount
FROM   customers c
LEFT JOIN orders o
  ON  o.customer_id = c.customer_id
  AND o.status = 'completed';

Aggregation collapses multiple rows into a single summary row using functions like COUNT, SUM, AVG, MIN, MAX, STRING_AGG, and ARRAY_AGG. GROUP BY partitions the rows into groups (defined by one or more columns), and aggregation then operates within each group independently.

The key rule of GROUP BY: after grouping, every expression in SELECT must be either:

1. One of the GROUP BY columns (or a deterministic function of them), or
2. An aggregate expression.

This is because the engine has collapsed each group into one row — it cannot tell you the value of a non-grouped, non-aggregated column (which value would it pick from the group?).

Logical evaluation order is essential to remember:

1. FROM / JOIN — assemble the input
2. WHERE — filter individual rows before grouping
3. GROUP BY — partition into groups
4. HAVING — filter groups (can use aggregate functions)
5. SELECT — compute output expressions
6. ORDER BY — sort
7. LIMIT — truncate

This is why you cannot use a SELECT alias in a WHERE clause (WHERE runs before SELECT), but you can use it in ORDER BY (which runs after SELECT) in most databases.

HAVING vs WHERE: WHERE filters individual rows; HAVING filters aggregated groups. Use WHERE to reduce rows before aggregation (more efficient), use HAVING to filter on aggregate results.

Useful aggregate variants:

• COUNT(*) — count all rows in the group
• COUNT(col) — count non-NULL values in col
• COUNT(DISTINCT col) — count distinct non-NULL values
• SUM(CASE WHEN condition THEN 1 ELSE 0 END) — conditional count (a powerful pattern)
• Most aggregates accept FILTER (WHERE condition) in PostgreSQL, which is cleaner than the CASE pattern.

-- Total revenue per country, only for completed orders,
-- shown only for countries with > 5000 in revenue
SELECT   c.country_code,
         COUNT(*)           AS order_count,
         SUM(o.amount)      AS total_revenue,
         AVG(o.amount)      AS avg_order_value
FROM     customers c
JOIN     orders o ON o.customer_id = c.customer_id
WHERE    o.status = 'completed'        -- filter BEFORE grouping
GROUP BY c.country_code
HAVING   SUM(o.amount) > 5000          -- filter AFTER grouping
ORDER BY total_revenue DESC;

-- Conditional aggregation: count completed vs cancelled in one scan
SELECT
  customer_id,
  COUNT(*) FILTER (WHERE status = 'completed')  AS completed,
  COUNT(*) FILTER (WHERE status = 'cancelled')  AS cancelled
FROM orders
GROUP BY customer_id;

-- Equivalent using CASE (portable to all SQL dialects)
SELECT
  customer_id,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id;

A subquery (also called an inner query or nested query) is a SELECT statement embedded within another SQL statement. Subqueries make SQL compositional — the output of one query becomes the input of another.

Scalar subqueries appear in SELECT or WHERE and return exactly one row and one column. If they return more than one row, it is a runtime error.

Row subqueries return one row with multiple columns; used in WHERE with row constructors.

Table subqueries (derived tables) appear in FROM and return a full relation. You must alias them.

Correlated subqueries reference a column from the outer query. The inner query is conceptually re-executed once per row of the outer query, making them potentially expensive. However, query planners can often decorrelate them into joins.

IN, NOT IN, EXISTS, NOT EXISTS are the key predicates for subqueries in WHERE:

• col IN (SELECT ...) — true if col equals any value returned. Beware: NOT IN (...) returns UNKNOWN for all rows if the subquery returns any NULL, which is almost never what you want. Use NOT EXISTS instead.
• EXISTS (SELECT 1 FROM ...) — true if the subquery returns at least one row. EXISTS short-circuits after the first match and is generally more efficient and NULL-safe than IN for existence checks.
• ALL, ANY/SOME — amount > ALL (SELECT ...) means greater than every value returned.

Knowing when to use a subquery vs. a join is a key skill. Often a join is more efficient and explicit, but a well-placed subquery can be much clearer. A correlated subquery in WHERE is almost always replaceable by a join or a window function — and usually should be, for performance.

-- Scalar subquery in SELECT
SELECT
  order_id,
  amount,
  (SELECT AVG(amount) FROM orders) AS overall_avg,
  amount - (SELECT AVG(amount) FROM orders) AS diff_from_avg
FROM orders;

-- Derived table (subquery in FROM)
SELECT country_code, avg_revenue
FROM (
  SELECT c.country_code, AVG(o.amount) AS avg_revenue
  FROM   customers c
  JOIN   orders o ON o.customer_id = c.customer_id
  GROUP BY c.country_code
) AS country_stats
WHERE avg_revenue > 200;

-- EXISTS (preferred over IN for existence checks)
SELECT email
FROM   customers c
WHERE  EXISTS (
  SELECT 1
  FROM   orders o
  WHERE  o.customer_id = c.customer_id
    AND  o.amount > 1000
);

-- NOT IN NULL trap (avoid!)
-- If orders contains ANY NULL customer_id, this returns nothing
SELECT email FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- Safe version with NOT EXISTS
SELECT email FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

Window functions are arguably the most powerful feature in modern SQL, and the one most developers discover late. They perform a calculation across a set of related rows (the "window") while preserving all rows in the output — unlike GROUP BY, which collapses them.

The syntax is: function() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ...)

• PARTITION BY: like GROUP BY but for the window — divides rows into groups within which the function operates. Omitting it makes the entire result set one partition.
• ORDER BY (inside OVER): defines the logical ordering within each partition. Required for ranking and running-total functions, optional for others.
• Frame clause (ROWS/RANGE BETWEEN): defines which rows relative to the current row are included. The default when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (a running total frame). Use ROWS BETWEEN for precise row-count offsets.

Ranking functions: ROW_NUMBER() — unique integer per row; RANK() — same rank for ties, gaps after; DENSE_RANK() — same rank for ties, no gaps; NTILE(n) — bucket into n equal groups.

Offset functions: LAG(col, n) — value of col n rows before the current row; LEAD(col, n) — n rows after. Essential for period-over-period comparisons.

Aggregate as window: any aggregate function (SUM, AVG, MIN, MAX, COUNT) can be used as a window function, enabling running totals, moving averages, and partition-wide summaries alongside row-level detail.

Execution order: window functions execute after GROUP BY and HAVING but before ORDER BY and LIMIT. This means you cannot filter on a window function result in WHERE or HAVING — wrap the query in a CTE or subquery.

This is the feature that replaced the most painful correlated subqueries and self-joins in analytics SQL. If you see a query computing "rank within group" or "running total" without window functions, it is almost certainly inefficient and complex — window functions are the right tool.

-- ROW_NUMBER: top-1 order per customer (no ties kept)
SELECT *
FROM (
  SELECT
    order_id,
    customer_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id
                       ORDER BY amount DESC) AS rn
  FROM orders
) ranked
WHERE rn = 1;

-- Running total and moving 3-order average
SELECT
  order_id,
  placed_at,
  amount,
  SUM(amount)  OVER (ORDER BY placed_at
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                   ) AS running_total,
  AVG(amount)  OVER (ORDER BY placed_at
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
                   ) AS moving_avg_3
FROM orders
ORDER BY placed_at;

-- Month-over-month revenue change with LAG
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month)           AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS delta
FROM monthly_revenue
ORDER BY month;

-- Percentage of partition total (no GROUP BY collapse)
SELECT
  customer_id,
  order_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
  ROUND(100.0 * amount /
        SUM(amount) OVER (PARTITION BY customer_id), 2) AS pct_of_customer
FROM orders;

A Common Table Expression (CTE) — introduced with the WITH keyword — is a named, temporary result set that exists only for the duration of a single SQL statement. Think of it as a named subquery defined at the top of your query, which you can then reference one or more times in the main query body.

CTEs serve two distinct purposes:

1. Clarity and composability (non-recursive CTEs): Breaking a complex query into named logical steps dramatically improves readability. Instead of nesting five derived tables, you write five named transformations in sequence, each building on the last. Unlike a view, a CTE does not persist. Unlike a subquery, it can be referenced multiple times without re-running. Whether the database materializes the CTE or inlines it depends on the implementation — PostgreSQL generally inlines non-recursive CTEs (post v12), treating them as optimization fences only if explicitly told to materialize.

2. Recursion: A recursive CTE consists of two parts joined by UNION ALL:

• The anchor member (base case): a non-recursive SELECT that seeds the recursion.
• The recursive member: a SELECT that references the CTE itself, adding new rows to the result.

The engine executes the anchor, then repeatedly executes the recursive member using the previous iteration's output as input, until no new rows are produced. This is how SQL handles hierarchical and graph data — organization charts, file system trees, Bill of Materials, graph reachability.

Caution: recursive CTEs require a termination condition. Without one (or with a cycle in the data), the recursion is infinite. PostgreSQL has a CYCLE clause (SQL:1999) to detect cycles; alternatively, track visited nodes in an array.

Recursive CTEs correspond to transitive closure in relational algebra — something that flat relational algebra cannot express, making recursion a genuine extension of the relational model's expressive power.

-- Non-recursive: pipeline of named transformations
WITH
completed_orders AS (
  SELECT customer_id, amount
  FROM   orders
  WHERE  status = 'completed'
),
customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM   completed_orders
  GROUP BY customer_id
),
high_value AS (
  SELECT customer_id, total
  FROM   customer_totals
  WHERE  total > 1000
)
SELECT c.email, hv.total
FROM   high_value hv
JOIN   customers c USING (customer_id)
ORDER BY hv.total DESC;

-- Recursive CTE: traverse org chart (manager hierarchy)
WITH RECURSIVE org_tree AS (
  -- Anchor: start at the CEO (no manager)
  SELECT employee_id, name, manager_id, 0 AS depth
  FROM   employees
  WHERE  manager_id IS NULL

  UNION ALL

  -- Recursive: add direct reports of the previous level
  SELECT e.employee_id, e.name, e.manager_id, ot.depth + 1
  FROM   employees e
  JOIN   org_tree ot ON ot.employee_id = e.manager_id
)
SELECT depth, name
FROM   org_tree
ORDER BY depth, name;

A view is a named, saved SELECT statement stored in the database catalog. When you query a view, the database (in most cases) substitutes the view's definition into the query and optimizes the whole thing together — views are not stored copies of data but stored queries.

Views serve several important purposes:

Abstraction and encapsulation: A view hides complex join logic or business rules behind a simple name. If the underlying schema changes, you update the view definition rather than every query that depends on it.

Security: You can grant users access to a view without granting access to the underlying tables. A customer_summary view can expose aggregated data while hiding individual row details or sensitive columns like payment information.

Reuse without persistence: Unlike a CTE, a view persists across sessions and can be shared across many queries and users.

Updatable views: Simple views (a single table, no aggregation or distinct) can be INSERT/UPDATE/DELETE targets — the database translates the operation back to the base table. Complex views are typically read-only, though PostgreSQL supports INSTEAD OF triggers on views for custom write behavior.

Materialized Views: A MATERIALIZED VIEW (PostgreSQL, Oracle) actually stores the result set on disk. Queries against it are fast because no computation is needed — the database reads pre-computed data. The tradeoff is that materialized views go stale and must be refreshed (REFRESH MATERIALIZED VIEW). This is a common pattern for pre-aggregating analytics data: refresh nightly, query instantly.

A good rule of thumb: if you find yourself writing the same complex subquery in multiple places, it should probably be a view (or a CTE if it is query-local).

-- Create a view: encapsulates a common join
CREATE VIEW order_details AS
SELECT
  o.order_id,
  o.amount,
  o.status,
  o.placed_at,
  c.email       AS customer_email,
  c.country_code
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

-- Query the view just like a table
SELECT customer_email, SUM(amount)
FROM   order_details
WHERE  status = 'completed'
GROUP BY customer_email;

-- Materialized view for fast analytics
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  DATE(placed_at) AS day,
  SUM(amount)     AS revenue,
  COUNT(*)        AS order_count
FROM   orders
WHERE  status = 'completed'
GROUP BY DATE(placed_at)
ORDER BY day;

-- Refresh when underlying data changes
REFRESH MATERIALIZED VIEW daily_revenue;

-- Drop a view
DROP VIEW IF EXISTS order_details;
DROP MATERIALIZED VIEW IF EXISTS daily_revenue;

SQL provides three set operations that combine the results of two SELECT statements:

UNION: returns all rows from both queries, eliminating duplicates (like mathematical union). UNION ALL keeps duplicates and is faster — use it when you know there are no duplicates or you want to count them.

INTERSECT: returns only rows that appear in both result sets (like mathematical intersection).

EXCEPT (MINUS in Oracle): returns rows from the first result set that do not appear in the second.

All three require the two queries to have the same number of columns with compatible types — the column names come from the first query.

These operations correspond exactly to the set operations in relational algebra, making them natural. They are less commonly needed than joins and aggregation but essential when:

• Combining data from two separate tables of the same shape (UNION ALL for audit logs, UNION for deduplicating)
• Finding overlap between two cohorts (INTERSECT)
• Finding what exists in one set but not another (EXCEPT, though LEFT JOIN / NOT EXISTS is usually clearer and more efficient)

A common pitfall: UNION de-duplicates by comparing all columns, which may not match your intended semantics. And because deduplication requires sorting or hashing, UNION is expensive — always prefer UNION ALL when you know the inputs are disjoint.

-- UNION: combine two queries into one result, deduplicating
-- Find all email addresses that appear in customers OR vendors
SELECT email FROM customers
UNION
SELECT email FROM vendors;

-- UNION ALL: keep duplicates (faster, use when inputs are disjoint)
SELECT 'customer' AS type, email FROM customers
UNION ALL
SELECT 'vendor'   AS type, email FROM vendors;

-- INTERSECT: emails in BOTH tables
SELECT email FROM customers
INTERSECT
SELECT email FROM vendors;

-- EXCEPT: customers who are NOT vendors
SELECT email FROM customers
EXCEPT
SELECT email FROM vendors;

-- Equivalent using NOT EXISTS (often more readable and efficient)
SELECT email FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM vendors v WHERE v.email = c.email
);