In SQL, single equal sign (=) is used for equality checks in the WHERE clause, not == or ===, which are JavaScript operators.

The correct syntax is SELECT * FROM users WHERE name = 'John';.

COUNT(column_name) counts the number of non-NULL values in a specified column. To count all rows, including NULLs, use COUNT(*).

You can also use COALESCE to ensure NULLs default to a non-NULL value. Something like: COUNT(COALESCE(column_name, 0)).

A LEFT JOIN returns all rows from the left table and matched rows from the right table, with NULL for unmatched rows in the right table.

An INNER JOIN returns rows where there is a match in both tables. Unmatched rows are not included in the result set.

A correlated subquery is evaluated once per row of the outer query. It references columns from the outer query, making it dependent on each row.

The WITH clause, or Common Table Expression (CTE), is used to define a temporary result set that can be referenced within the main query.

This can help improve readability and maintainability of complex queries.

The syntax is:

WITH cte_name AS (
  SELECT column_name
  FROM table_name
)
SELECT *
FROM cte_name;

IS NULL checks if a specified column contains a NULL value. IS NOT NULL checks the opposite.

The IN operator filters rows to match any values in a specified list, often as an alternative to multiple OR conditions.

While it might return “unique rows” (and often does), it’s not necessarily the primary purpose.

Indexing is not required here, though it’s certainly a best practice to use IN on a column with an index, preferably a UNIQUE index, as it can improve performance.

The COALESCE function returns the first non-NULL value in a specified list, useful for substituting default values when NULLs are encountered.

For example, COALESCE(column_name, 0) would return 0 if column_name is NULL.

The GROUP BY clause groups rows that have the same values in specified columns, allowing aggregate functions to be applied on each group.

A FULL OUTER JOIN returns all rows from both tables, filling unmatched rows with NULLs for missing values.

This is useful for comparing two tables and finding differences between them.