Interview Questions
A JOIN clause is used to combine rows from two or more tables based on a related column between them. Its basic purpose is to create a result set that shows how data in different tables is related.
The four main types of JOIN operations in SQL are INNER JOIN, LEFT (OUTER) JOIN, RIGHT (OUTER) JOIN, and FULL (OUTER) JOIN. Each type determines how records from the joined tables are combined in the result set.
INNER JOIN returns only the matching rows from both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for the right table columns.
A foreign key constraint is a column that references the primary key of another table. It maintains referential integrity and ensures data consistency between related tables.
A self-join is when a table is joined with itself. It's useful when a table contains hierarchical or self-referential data, such as an employee table where each employee has a manager who is also an employee.
A cross join produces a Cartesian product of two tables, combining each row from the first table with every row from the second table. The result contains all possible combinations of rows from both tables.
A NATURAL JOIN automatically joins tables based on columns with the same name in both tables, while an INNER JOIN requires explicit specification of the join conditions using the ON clause.
A composite key is a combination of two or more columns that uniquely identify a row. In relationships, it can be used as a foreign key to reference another table where the same combination of columns serves as the primary key.
Referential integrity ensures that relationships between tables remain consistent. It prevents actions that would destroy relationships, such as deleting a record that's referenced by other records or adding a reference to a nonexistent record.
The main types of relationships are one-to-one (1:1), one-to-many (1:N), and many-to-many (M:N). Each type determines how records in one table relate to records in another table.
A many-to-many relationship is implemented using a junction table (also called bridge or associative table) that contains foreign keys referencing the primary keys of both related tables.
ON DELETE CASCADE automatically deletes related records in the child table when a parent record is deleted, while ON DELETE SET NULL sets the foreign key fields to NULL in the child table when the parent record is deleted.
A recursive relationship is when a table has a relationship with itself, where a record in the table references another record in the same table, such as an employee having a manager who is also an employee.
Cardinality defines the numerical relationship between records in related tables, specifying how many records in one table can be related to a record in another table (e.g., one-to-one, one-to-many, many-to-many).
An anti-join returns records from the first table that have no matching records in the second table. It can be implemented using NOT EXISTS, NOT IN, or LEFT JOIN with a NULL check on the second table's columns.
NULL values in JOIN conditions require special attention as they don't match anything, even other NULLs. You may need to use IS NULL in the join condition or COALESCE to handle NULL values appropriately.
A surrogate key is an artificial primary key, typically an auto-incrementing number, used instead of a natural key. It's useful when natural keys are complex, subject to change, or non-existent.
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It often results in creating more tables with relationships between them, requiring JOINs to retrieve related data.
There is no difference - LEFT OUTER JOIN and LEFT JOIN are synonymous in SQL. The word OUTER is optional and both produce the same result, returning all records from the left table and matching records from the right table.
Indexes on JOIN columns can significantly improve JOIN performance by reducing the need for full table scans. The database can use indexes to quickly locate matching rows between tables.
A non-equi join is a join that uses comparison operators other than equality (!=, >, <, etc.). It's useful when you need to match records based on ranges or conditions rather than exact matches.
A one-to-one relationship is implemented using a unique foreign key constraint in one table that references the primary key of another table. This ensures that each record in one table corresponds to exactly one record in the other table.
A circular reference occurs when tables form a cycle of foreign key relationships. It can be prevented by careful database design, breaking the cycle, or using alternative relationship patterns.
The USING clause is a shorthand for joining tables when the columns have the same name in both tables. It simplifies the JOIN syntax by eliminating the need for an ON clause with equality comparison.
When joining tables with composite foreign keys, all components of the key must be included in the JOIN condition using AND operators to ensure the correct matching of records.
Denormalization is the process of adding redundant data to tables to reduce the need for JOINs. While it can improve query performance, it introduces data redundancy and potential consistency issues.
Complex JOINs can be optimized by proper indexing, joining tables in the most efficient order, using appropriate JOIN types, and considering denormalization where necessary. The execution plan should be analyzed to identify performance bottlenecks.
UNION combines rows from two or more queries vertically (adding rows), while JOIN combines tables horizontally (adding columns). UNION requires the same number and compatible types of columns in all queries.
Referential actions specify what happens when a referenced record is deleted or updated. Types include CASCADE, SET NULL, SET DEFAULT, and NO ACTION, each defining different behaviors for maintaining referential integrity.
Logical joins describe the desired relationship between tables in the query, while physical joins refer to the actual methods used by the database engine to combine the data, such as nested loops, hash joins, or merge joins.