JOIN and UNION are both used to combine data from multiple tables, but they serve different purposes. JOIN is used to combine rows from two or more tables based on a related column between them. Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. UNION, on the other hand, is used to combine the result sets of two or more SELECT queries into a single result set.

Unlike JOIN, UNION removes duplicates by default and requires the same number of columns and compatible data types across all queries.

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves the readability and maintainability of complex queries.

CTEs are defined using the WITH keyword and exist only during the execution of a single query. They are particularly useful for recursive queries, as they allow for better organization of logic. A CTE can be referenced multiple times in the same query, unlike subqueries, which are evaluated every time they are used. This makes CTEs more efficient in certain cases.

INNER JOIN returns only the rows where there is a match in both tables. If there’s no matching row in one table, that row will not be included in the result set. LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table.

If no match is found, the result will contain NULL values for columns from the right table. LEFT JOIN ensures that all rows from the left table are included, even if there’s no corresponding match in the right table.

A subquery is a query embedded within another query. It is usually placed inside the WHERE, FROM, or SELECT clause of the main query. Subqueries can be classified into two types: scalar (returns a single value) and table (returns multiple rows and columns). They are executed once for the entire query and can be used to perform operations like filtering, calculating aggregate values, or comparing results.

Subqueries can be nested within each other but should be used carefully as they can sometimes lead to performance issues when not optimized.

Both HAVING and WHERE are used to filter records in SQL, but they are used in different contexts. The WHERE clause filters records before any grouping occurs, while the HAVING clause filters records after grouping (i.e., it works with GROUP BY). The WHERE clause is used to filter individual rows, whereas the HAVING clause is used to filter groups of rows that have been aggregated.

For example, in a query that counts sales by employee, WHERE would filter employees, and HAVING would filter those with a count greater than a certain number.

An index is a data structure used to improve the speed of data retrieval operations on a database table. It works by providing quick access to rows based on the values of one or more columns. Without an index, the database must perform a full table scan to locate the data, which can be slow for large tables.

Indexes speed up SELECT queries, but they come with a trade-off: they slow down DML operations like INSERT, UPDATE, and DELETE, as the index must be updated each time the data changes. Common types of indexes include B-tree and hash indexes.

Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and establishing relationships between them through foreign keys. Normalization is typically done in stages, with common normal forms being 1NF, 2NF, and 3NF. Denormalization, on the other hand, is the process of merging tables to optimize read performance by reducing the number of joins.

While normalization minimizes redundancy, denormalization introduces some redundancy to speed up query performance, especially in read-heavy systems, at the cost of potentially increased data anomalies during updates.

The DISTINCT keyword is used to eliminate duplicate values from the result set. It ensures that the query returns only unique rows, based on the columns specified in the SELECT statement.

For example, when selecting data from a table with duplicate rows, DISTINCT filters out the duplicates, returning only one instance of each row. It can be used on one or more columns to identify unique combinations of values. However, the use of DISTINCT can negatively impact performance on large datasets because it requires additional processing to remove duplicates.

A self-join is a type of join where a table is joined with itself. This is useful when you need to compare rows within the same table. Typically, a self-join is used with an alias to differentiate the two instances of the same table.

For example, in an employee table where each employee has a manager (who is also an employee), a self-join can be used to retrieve a list of employees and their respective managers. The query involves using the table twice with different aliases to simulate a relationship between rows in the same table.

Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not collapse the result set but instead return a value for each row based on its window of data.

Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(). These functions are often used in analytical queries to calculate running totals, moving averages, or rankings. The OVER() clause defines the window of data over which the function operates, and it can include partitioning and ordering to control the scope of the function.

• DELETE: Removes rows from a table based on a condition but can be rolled back if wrapped in a transaction. It maintains table structure and does not reset identity columns.
• TRUNCATE: Removes all rows from a table but cannot be rolled back unless inside a transaction. It is faster than DELETE and resets identity columns.
• DROP: Completely removes a table, including its structure, data, and associated constraints. It cannot be undone unless the table is recreated.

SQL injection is a code injection technique where an attacker can execute malicious SQL statements to manipulate a database. It typically occurs when user input is directly included in SQL queries without proper sanitization or validation.

To prevent SQL injection, always use parameterized queries or prepared statements, which separate SQL logic from user inputs. Additionally, applying proper input validation, using stored procedures, and limiting database permissions can help reduce the risk. Avoid concatenating user input directly into queries and ensure that the database system is up to date with security patches.

• CHAR is a fixed-length data type. It always stores data with the specified length, padding it with spaces if necessary.

• VARCHAR is a variable-length data type, meaning it only stores the actual characters, and the storage size adjusts based on the length of the data.VARCHAR is generally preferred for variable-length data as it is more storage-efficient. However, if you need a fixed-length string, CHAR can be more efficient in some cases.

The EXPLAIN statement is used to obtain the execution plan for a query. It provides details on how SQL engines execute a query, including which indexes are used, join methods, and the order of operations. By analyzing the output of EXPLAIN, developers can identify performance bottlenecks, such as full table scans or inefficient joins.

This can help optimize queries by creating the right indexes, restructuring the query, or modifying table structures. EXPLAIN is commonly used for performance tuning in databases with large datasets.

A trigger is a database object that automatically executes or fires a specified action when certain events occur, such as an insert, update, or delete operation. Triggers are often used to enforce business rules, validate data, or synchronize tables.

For example, a trigger might update a "last modified" timestamp whenever a row in a table is updated. Triggers can be set to fire before or after an operation, depending on the desired behavior. While powerful, triggers should be used carefully, as they can introduce hidden logic and affect performance.

The AND and OR operators are logical operators used in SQL to filter results based on multiple conditions. The AND operator returns true if all conditions are true, whereas the OR operator returns true if at least one condition is true.

AND narrows the result set by requiring that all conditions be satisfied, while OR broadens the result set by including rows where any of the conditions are satisfied. The precedence of these operators can affect query results, so it’s often necessary to use parentheses to clarify the order of evaluation.

The GROUP BY clause is used to group rows that have the same values into summary rows, like aggregating data. For example, it’s commonly used to calculate aggregate functions such as COUNT(), SUM(), AVG(), etc., on grouped data. GROUP BY is typically used in conjunction with aggregate functions to create summaries or reports.

It allows you to group data by one or more columns, with the results representing each unique group. If no grouping is required, the GROUP BY clause can be omitted.

The UPDATE statement is used to modify existing records in a table based on a condition, while the INSERT INTO statement is used to add new rows to a table. UPDATE affects only rows that meet the specified criteria and modifies existing values, whereas INSERT INTO adds entirely new records to the table.

Both statements are critical for managing data, but they serve distinct purposes: UPDATE is for modifying data, and INSERT INTO is for adding new data.

Data consistency ensures that data in a database remains accurate and reliable. It is one of the key principles of the ACID properties (Atomicity, Consistency, Isolation, Durability) in database transactions. In SQL, consistency means that a database starts in a valid state and remains in a valid state after each transaction.

Transactions should not leave the database in a half-committed state, and any data manipulation should maintain business rules, constraints, and referential integrity. Ensuring data consistency is critical for preventing anomalies like duplicate records, incorrect relationships, or invalid data.

In SQL, IS NULL is used to test whether a value is NULL, while = NULL is not valid for comparisons. NULL is not a value but represents the absence of a value, so it cannot be compared using traditional comparison operators like =. Instead, you use IS NULL to check if a column contains a NULL value. Conversely, IS NOT NULL checks for the presence of a value.

For example, to find records where a column has no value, you would use WHERE column_name IS NULL.

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. It must contain unique values and cannot have NULL values. A table can have only one primary key. A unique key, on the other hand, also ensures uniqueness of values in a column but can allow NULL values (depending on the database).

Unlike the primary key, a table can have multiple unique keys. Both keys are used to maintain data integrity by preventing duplicate entries.

An auto-increment column is a column in a database table that automatically generates a unique value when a new row is inserted. It is typically used for primary key columns. When a new record is added, the database engine automatically increments the value of the column by a specified step (usually 1).

The auto-increment feature ensures that each new row receives a unique identifier without needing to manually specify the value. Different databases have different syntax for setting up auto-increment columns, such as AUTO_INCREMENT in MySQL or SERIAL in PostgreSQL.

A recursive CTE (Common Table Expression) is a CTE that references itself to perform hierarchical queries, often used for traversing tree-like structures such as organizational charts or folder directories.

It consists of two parts: the anchor member (which selects the base case) and the recursive member (which references the CTE itself). The recursion continues until a base condition is met. Recursive CTEs are written using the WITH keyword followed by a UNION ALL operator to combine the results of each recursive step. They are essential for operations like calculating organizational hierarchies or finding the shortest path in a graph of data.

The CASE expression in SQL is a conditional statement that allows you to perform IF-ELSE logic within a query. It can be used to create new calculated columns or to conditionally modify data based on specified criteria. The CASE statement has two types: simple CASE and searched CASE. In the simple version, it compares a column against a set of conditions.

In the searched version, it evaluates each condition independently. A typical use case is to categorize numerical data, like converting a range of scores into grade categories (A, B, C). The result of the CASE expression is based on which condition evaluates to true.

A FULL OUTER JOIN returns all rows from both the left and right tables. Where there is a match, it combines the rows, but where there is no match, it returns NULL values for the columns of the table that doesn't have a matching row.

This join is useful when you need to find records that have no corresponding match in one of the tables. In contrast to INNER JOIN (which only returns matching rows) and LEFT/RIGHT JOIN (which return unmatched rows from one table), FULL OUTER JOIN ensures all records from both tables are included.