SQL Interview Preparation

1. What is the difference between INNER JOIN and OUTER JOIN

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Eg.

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5  ssuming you're joining on columns with no duplicates, which is a very common case:

2. What is the difference between UNION and OUTERJOIN

UNION puts lines from queries after each other, while JOIN makes a cartesian product and subsets it -- completely different operations. Trivial example of UNION:

mysql> SELECT 23 AS foo
    -> UNION
    -> SELECT 45 AS foo;
+-----+
| bah |
+-----+
|  23 | 
|  45 | 
+-----+
2 rows in set (0.00 sec)

similarly trivial example of JOIN:

mysql> SELECT * FROM 
    -> (SELECT 23 AS bah) AS foo 
    -> JOIN 
    -> (SELECT 45 AS bah) AS bar
    -> ON (33=33);
+-----+-----+
| foo | bar |
+-----+-----+
|  23 |  45 | 
+-----+-----+
1 row in set (0.01 sec)

3. What is the difference between UNION and UNION ALL

* The UNION command combines the result set of two or more SELECT statements (only distinct values)

* Union removes duplicate records

* The `UNION ALL` command combines the result set of two or more SELECT statements (allows duplicate values).

* the query would skip the duplicates removal step and return the results as below

Eg.

UNION or UNION ALL have the same basic requirements of the data being combined:

* There must be the same number of columns retrieved in each SELECT statement to be combined.

* The columns retrieved must be in the same order in each SELECT statement.

* The columns retrieved must be of similar data types.

What is the difference between Primary key and Unique key
Primary Key is a column that is used to uniquely identify each tuple of the table.
It is used to add integrity constraints to the table. Only one primary key is allowed to be used in a table. Duplicate and NULL (empty) values are not valid in the case of the primary key. Primary keys can be used as foreign keys for other tables too.
Let’s take an example,
We have a table name employee which stores data of employees of a company. The below table shows the contents of the table.
Emp_id Name Ph_No. Position Salary
Emp_id here is primary key of the table. As the id of each employee is unique and no two employees can have the same Emp_id.
Unique Key
Unique key is a constraint that is used to uniquely identify a tuple in a table.
Multiple unique keys can present in a table. NULL values are allowed in case of a unique key. These can also be used as foreign keys for another table.
Let’s take an example,
We have a table name employee which stores data of employees of a company. The below table shows the contents of the table.
Emp_id Name Ph_No. Position Salary
Ph_No here is the foriegn key of the table. As the phone number of each employee is unique and it might be possible that an employee does not have any phone number.
Difference between Primary Key and Unique Key

Primary Key	Unique Key
Unique identifier for rows of a table	Unique identifier for rows of a table when primary key is not present
Cannot be NULL	Can be NULL
Only one primary key can be present in a table	Multiple Unique Keys can be present in a table
Selection using primary key creates clustered index	Selection using unique key creates non-clustered index

Clustered index vs Non Clustered index

Where is clustered index stored

In Microsoft SQL Server, a clustered index is stored in the data pages of the table for which it is created. When a clustered index is created on a table, the data rows of the table are sorted and stored on the data pages in the order of the clustered index key. The data pages are then organized in a B-tree (a self-balancing tree data structure) structure, where the leaf nodes of the B-tree contain the data rows of the table. The leaf nodes of the B-tree are linked together in the order of the clustered index key, so that data can be retrieved quickly by searching the B-tree using the clustered index key. The root and intermediate pages of the B-tree contain the index rows that point to the leaf nodes.

It's worth noting that the the physical location of the data on disk is determined by the clustered index. The table data is stored in the leaf nodes of the clustered index and the data rows in the table are stored in the same order as the clustered index. So, the first data row in the table will also be the first leaf node in the clustered index and so on.

Where is non clustered index stored

In Microsoft SQL Server, a non-clustered index is stored separately from the data table for which it is created. The data rows of the table are not physically rearranged to match the order of the non-clustered index. Instead, a separate data structure called the non-clustered index is created and maintained that contains a copy of the indexed columns and a pointer (row locater) to the corresponding row in the data table.

The non-clustered index is also organized in a B-tree structure, similar to the clustered index. The leaf nodes of the B-tree contain the index rows that include the indexed columns and the pointer (row locater) to the corresponding data row in the table. The root and intermediate pages of the B-tree contain the index rows that point to the leaf nodes.

A table can have multiple non-clustered indexes, each one stored in a separate B-tree. And like the clustered index, a non-clustered index can also be stored in a separate filegroup from the data table, which can be useful for performance and manageability.

Search This Blog

San