SQL Server Interview Questions

Question: What are INNER JOIN and OUTER JOIN in SQL?

Answer:

INNER JOIN and OUTER JOIN are both used in SQL to combine rows from two or more tables based on a related column between them. However, they differ in how they handle matching and non-matching rows.

---

1. INNER JOIN:

An INNER JOIN returns only the rows where there is a match in both tables based on the specified condition.

• If a row from the left table does not have a matching row in the right table, it will not appear in the result set.
• It is the most common type of join.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

Example:

Consider two tables, Employees and Departments:

Employees Table:

EmployeeID	Name	DepartmentID
1	John	101
2	Sarah	102
3	David	103
4	Michael	NULL

Departments Table:

DepartmentID	DepartmentName
101	HR
102	IT
103	Finance

Query:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Sarah	IT
David	Finance

• Michael does not appear because his DepartmentID is NULL, and no matching department exists for him.

---

2. OUTER JOIN:

An OUTER JOIN returns all rows from at least one table and the matching rows from the other table. If there is no match, the result will contain NULL values for the columns from the table that does not have a match.

There are three types of OUTER JOIN:

• LEFT OUTER JOIN (or simply LEFT JOIN)
• RIGHT OUTER JOIN (or simply RIGHT JOIN)
• FULL OUTER JOIN

---

2.1 LEFT OUTER JOIN (LEFT JOIN):

A LEFT OUTER JOIN returns all the rows from the left table (table1) and the matching rows from the right table (table2). If there is no match in the right table, the result will contain NULL values for columns from the right table.

Syntax:

SELECT columns
FROM table1
LEFT OUTER JOIN table2 ON table1.column = table2.column;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Sarah	IT
David	Finance
Michael	NULL

• Michael is included, but since there is no matching DepartmentID, the DepartmentName is NULL.

---

2.2 RIGHT OUTER JOIN (RIGHT JOIN):

A RIGHT OUTER JOIN returns all the rows from the right table (table2) and the matching rows from the left table (table1). If there is no match in the left table, the result will contain NULL values for columns from the left table.

Syntax:

SELECT columns
FROM table1
RIGHT OUTER JOIN table2 ON table1.column = table2.column;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Sarah	IT
David	Finance
NULL	Marketing

• If there are departments without matching employees, those departments are still included in the result with NULL for the employee name.

---

2.3 FULL OUTER JOIN:

A FULL OUTER JOIN returns all rows when there is a match in one of the tables. If there is no match, the result will contain NULL values for the columns of the table without a match.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Example:

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result:

Name	DepartmentName
John	HR
Sarah	IT
David	Finance
Michael	NULL
NULL	Marketing

• In this example, Michael has no department, and Marketing has no employees, so NULL is shown for the unmatched values.

---

Summary of Differences:

Join Type	Returns	Example
INNER JOIN	Only matching rows from both tables	John, Sarah, David
LEFT JOIN	All rows from the left table and matching from the right table	John, Sarah, David, Michael
RIGHT JOIN	All rows from the right table and matching from the left table	John, Sarah, David, Marketing
FULL OUTER JOIN	All rows from both tables, with NULL for non-matching rows	John, Sarah, David, Michael, Marketing

---

Key Takeaways:

• INNER JOIN: Returns only the rows where there is a match in both tables.
• OUTER JOIN: Returns all rows from one or both tables, filling in NULL where there is no match.
  • LEFT JOIN: All rows from the left table and matching rows from the right.
  • RIGHT JOIN: All rows from the right table and matching rows from the left.
  • FULL OUTER JOIN: All rows from both tables, with NULL for unmatched rows.

Question: What is the SELECT INTO statement in SQL?

Answer:

The SELECT INTO statement in SQL is used to select data from one table and insert it into a new table in a single operation. This is especially useful when you want to create a new table based on the structure and data of an existing table.

The SELECT INTO statement creates a new table in the database and populates it with the selected data. The new table will have the same column definitions (data types) as the columns in the SELECT query.

---

Syntax:

SELECT column1, column2, ...
INTO new_table
FROM existing_table
WHERE condition;

• column1, column2, ...: The columns you want to select from the source table.
• new_table: The name of the new table that will be created.
• existing_table: The table from which you are selecting the data.
• condition: An optional condition to filter the rows you want to select (using the WHERE clause).

Key Points:

1. Creates a new table: The new table (new_table) does not have to exist before executing the SELECT INTO statement. If it does not exist, it is created automatically.
2. Data types are copied: The new table will have the same data types as the columns of the SELECT query from the existing table.
3. No indexes or constraints: The SELECT INTO statement does not copy indexes, primary keys, or foreign keys from the original table. Only the table structure and data are copied.
4. Works in SQL Server: SELECT INTO is commonly used in SQL Server, but some databases like MySQL or PostgreSQL may use a different syntax (such as CREATE TABLE AS SELECT).

---

Example 1: Basic SELECT INTO to create a new table

Suppose you have a table Employees, and you want to create a new table EmployeeBackup with the same structure and data:

Employees Table:

EmployeeID	Name	Department
1	John	HR
2	Sarah	IT
3	David	Finance

Query:

SELECT EmployeeID, Name, Department
INTO EmployeeBackup
FROM Employees;

Result:

• A new table called EmployeeBackup is created with the same structure and data as the Employees table.

EmployeeBackup Table:

EmployeeID	Name	Department
1	John	HR
2	Sarah	IT
3	David	Finance

---

Example 2: SELECT INTO with a WHERE condition

You can also use a WHERE clause to filter the rows you want to copy into the new table.

Query:

SELECT EmployeeID, Name, Department
INTO ITDepartmentEmployees
FROM Employees
WHERE Department = 'IT';

Result:

• A new table ITDepartmentEmployees is created, but it will only contain the employees whose department is IT.

ITDepartmentEmployees Table:

EmployeeID	Name	Department
2	Sarah	IT

---

Limitations:

• Does not copy constraints: The SELECT INTO statement does not copy any primary keys, foreign keys, or indexes from the original table. These need to be added manually if necessary.
• Cannot be used with existing tables: The SELECT INTO statement creates a new table, so it cannot be used to insert data into an already existing table. For inserting into an existing table, you would use the INSERT INTO statement instead.

---

Conclusion:

• The SELECT INTO statement in SQL is useful for creating new tables and inserting data into them based on the result of a query.
• It is widely used in scenarios where you need to back up data, create temporary tables, or clone a subset of data from one table into another.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.