What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

• The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.

• The HAVING clause is used to filter values from a group.

What is a key difference between TRUNCATE and DELETE?

TRUNCATE is DDL (Data Definition Language) command and so cannot be rolled back. It deletes all rows in a table.

DELETE is Data Manipulation Language (DML) command and can be rolled back. It deletes rows in a table that satisfy WHERE condition.

That is ADIC?

• Atomicity or "all or nothing". If one part of the transaction fails, the entire transaction fails, and the database state is left unchanged.

• Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.

• Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially.

• Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.

What is an execution plan?

Execution plan is strategy of fetching data and preparing answer for a query.

It can be shown by query with EXPLAIN as prefix. Result of such query may differ from actual execution plan and some DB stores history of queries with plans.

NULL and ternary logic

Most operations with NULL produce UNDEFINED.

NULL <> 1 and NULL = 1 and NULL = NULL are UNDEFINED. To check for NULL use IS NULL or IS NOT NULL.

AND with FALSE argument return FALSE despite any other arguments values including UNDEFINED.

OR with TRUE argument return TRUE despite any other arguments values including UNDEFINED.

This query won't return rows with NULL field:

SELECT name FROM customers WHERE patent <> 2;

and should be rewritten into:

SELECT name FROM customers WHERE patent <> 2 OR patent in NULL;

Also NOT IN always FALSE if sub-table has NULL. This query may be resolved in empty result:

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders)

if customer_id is NULL for some orders. Correct query is:

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)

What are different types of JOIN?

There are INNER JOIN (default type of join if no specific JOIN type is specified), LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), FULL JOIN (or FULL OUTER JOIN), and CROSS JOIN.

What is the difference between UNION and UNION ALL?

UNION omit duplicate records while UNION ALL preserve all of them.