SQL Joins

JOIN

 Top 100+ Sql Server Interview Questions

SQL Joins is used to Compose the data of two table into a single view-

SQL Joins are Five Types-

      1- Inner Joins
      2- Left Outer Joins
      3- Right outer Joins
      4- Full Joins
      5- Self Joins

Inner Joins syntax

   select * from table A
   INNER join table B on A.ID=B.ID

Result- Only the common data from both table which have ID in both table
Example
1- Create table Student

Student
StudentID	Name	UID
1	Bill Gate	1100110001
2	SAM	1100110002
3	Jastin	1100110004
4	Bejos	1100110015
5	Rohit	1100110016
6	Sachin	1100110020
7	Virat	1100110021
8	Modi	1100110023
9	Trump	1100110026
10	Putin	1100110030

 

2- Create Table Standard

Standard
StudentID	Name
1	I
2	II
3	III
4	IV
5	V
6	VI
7	VII
8	VIII
9	IX
10	X
11	XI
12	XII

Query To get the List of Student and Class?

Select  Student.StudentName, 

Standard.Name as [Class name]
from Student
INNER JOIN Standard
ON Student.StudentID=Standard.StudentID

Left Joins syntax

   select * from table A
   LEFT outer join table B on A.ID=B.ID

Result- All data from table A and matched data from table B where id of a Exists in B

Example-
               SELECT  * from Dizsweb_Service CS
               LEFT  OUTER JOIN  Service S 

               ON S.ServiceID=cs.ServiceID
               WHERE  S.Description IN

                               (

                              'dizsweb - Mailed Certificates- SQL',
                             'dizsweb - Returned Mail - SQL',
                             'dizsweb - Certificate Uploaded - SQL',
                             'dizsweb - Registration Letter - SQL'',
                             'dizsweb - Implementation Fee - SQL'',
                             'dizsweb- Monthly Fee - SQL',
                             'dizsweb - Overage Charge- SQL',
                             'dizsweb - Reimbursable Expenses- SQL'

                              );   

Rights Joins Syntax

   select * from table A
   RIGHT outer join table B on A.ID=B.ID

Result- All data from table B and matched data from table A where id of a Exists in A

Full joins Joins Syntax

   select * from table A
   FULL OUTER join table B on A.ID=B.ID

Result- All data from table A and B and All A data whose ID not in B and A data whose ID not in B and Common of A& B no duplicate ID

Self  JoinsSyntax

   select * from Sawanchauhan A
   left outer join Sawanchauhan B
where A.PermanentCity=B.CurrentCity


Result- All data from table (SawanChauhan) where permanenet city and current city are same only.




Create a Dummy database-

USE BookStore

CREATE TABLE Books

(

Id INT PRIMARY KEY IDENTITY(1,1),

Name VARCHAR (50) NOT NULL,

Price INT,

CategoryId INT,

AuthorId INT

)

 

 

Create a Table--

USE BookStore

CREATE TABLE Categories

(

Id INT PRIMARY KEY,

Name VARCHAR (50) NOT NULL,

)

 

USE BookStore

CREATE TABLE Authors

(

Id INT PRIMARY KEY,

Name VARCHAR (50) NOT NULL,

)

INSERT INTO Categories

VALUES (1, 'Cat-A'),

(2, 'Cat-B'),

(3, 'Cat-C'),

(7, 'Cat-D'),

(8, 'Cat-E'),

(4, 'Cat-F'),

(10,'Cat-G'),

(12,'Cat-H'),

(6, 'Cat-I')

 

 

 

 

INSERT INTO Authors

VALUES (1, 'Author-A'),

(2, 'Author-B'),

(3, 'Author-C'),

(10, 'Author-D'),

(12, 'Author-E')

 

 

 

 

 

 

 

 

 

 

INSERT INTO Books

VALUES ( 'Book-A', 100, 1, 2),

( 'Book-B', 200, 2, 2),

( 'Book-C', 150, 3, 2),

( 'Book-D', 100, 3,1),

( 'Book-E', 200, 3,1),

( 'Book-F', 150, 4,1),

( 'Book-G', 100, 5,5),

( 'Book-H', 200, 5,6),

('Book-I', 150, 7,8)

 

 

 

 

 

 

How to use count in join query in sql server? --INNER JOIN

 

SELECT Books.CategoryId, Books.Name, Categories.Id, Categories.Name

FROM Books

INNER JOIN Categories

ON Books.CategoryId = Categories.Id



Count --

SELECT count(Books.CategoryId)

FROM Books

INNER JOIN Categories

ON Books.CategoryId = Categories.Id

 

How to use count in join query in sql server? --LEFT JOIN

 

SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name

FROM Books

LEFT JOIN Categories

ON Books.CategoryId = Categories.Id

 

Count --

SELECT count(Books.CategoryId)

FROM Books

LEFT JOIN Categories

ON Books.CategoryId = Categories.Id

 

 

How to use count in join query in sql server? --RIGHT JOIN

SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name

FROM Books

RIGHT JOIN Categories

ON Books.CategoryId = Categories.Id

 

Count --

SELECT count(Books.CategoryId)

FROM Books

RIGHT JOIN Categories

ON Books.CategoryId = Categories.Id

 

 

 

 

How to use count in join query in sql server? --FULL JOIN

SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name

FROM Books

FULL JOIN Categories

ON Books.CategoryId = Categories.Id

 

 

Count --

SELECT count(Books.CategoryId)

FROM Books

FULL JOIN Categories

ON Books.CategoryId = Categories.Id

 

 

 

 

 

 SQL Joins Based Top Interview Questions?

 

1. What is a SQL join and why do we need it?

SQL joins are used  to combine records from two or more than two tables in a database system. A join refers to combining the fields from two or more than two tables by using a value that is common to each of the tables which helps in reducing the repeated columns and data stored in columns of the same table. We need joins to get details about the referenced data and It creates a logical bonding between two or more tables. It is necessary to get data efficiently from multiple tables, that’s why we need SQL joins.

 

2. What are the different types of SQL joins? 

      1- Inner Joins
      2- Left Outer Joins
      3- Right outer Joins
      4- Full Joins
      5- Self Joins

3. What are Nested Joins in SLQ?

Answer:
This kind of join works for each tuple in the outer join relation, then it scans the entire inner-join relation and adds any tuple that matches the join-condition and the result will be displayed.

 

4. What is Merge Join in SQL?

Answer:
The Merge join (also known as sort-merge join) is a join process which is used in the application of a Relational Database Management System. The basic trick of a join process is to find each unique value of the join attribute, the set of tuples in every relation that output that value.

 

Introduction To SQL Joins Interview Questions And Answers

SQL refer as Structured Query Language is a language which is designed entirely for accessing databases, and it is the most popular with other Database languages. With SQL, we can create tables, change data, update and delete as per the business requirement. So, let’s start with the frequently asked SQL joins interview questions.

Below are the important set of SQL Joins Interview Questions that are divided in two part

Part 1 – SQL Joins Interview Questions(Basic)

The first part cover the basic interview questions

1. What is a SQL join and why do we need it?

Answer:
SQL joins are used to combine records from two or more than two tables in a database system. A join refers to combining the fields from two or more than two tables by using a value that is common to each of the tables which helps in reducing the repeated columns and data stored in columns of the same table. We need joins to get details about the referenced data and It creates a logical bonding between two or more tables. It is necessary to get data efficiently from multiple tables, that’s why we need SQL joins.

2. What are the different types of SQL joins? Give a brief introduction to them?

Answer:
There are total 5 joins in SQL, they are: –

a. Inner Join OR Join

This is a kind of join where we get all the records that match the condition in two or more than two tables and then the records in all the tables that didn’t match are not displayed. It displays only the matching entries of two or more tables.

b. Outer Join

There are three types of Outer Joins:
1. LEFT Join or LEFT Outer Join
This kind of join will return all the rows from the left table in combination with the matching records or rows from the right table.
If there are no matching columns then it will return NULL values.

2. RIGHT Join or RIGHT Outer Join
This kind of join will return all the rows from the right table in combination with the matching records or rows from the left table.
If there are no matching columns then it will return NULL values.

3. Full Join or Full Outer Join
This kind of JOIN combines Right Outer Join in addition to Left Outer Join. It displays records from the tables when the conditions are met and displays the NULL value when there is no match.

c. Natural Join
This kind of join has two conditions, first it removes duplicates records from the result and second this join will be made on all the columns with the same name for equality.

d. Cross Join
This kind of join gives the cartesian product of the two or more than two tables involved.

e. Self-Join
It is almost the same as the inner join but rather it is a join of a table to itself.

3. What are Nested Joins in SLQ?

Answer:
This kind of join works for each tuple in the outer join relation, then it scans the entire inner-join relation and adds any tuple that matches the join-condition and the result will be displayed.

Part 2 – SQL Joins Interview Questions (Advanced)

The second part covers the advanced interview questions

4. What is Merge Join in SQL?

Answer:
The Merge join (also known as sort-merge join) is a join process which is used in the application of a Relational Database Management System. The basic trick of a join process is to find each unique value of the join attribute, the set of tuples in every relation that output that value.

5. What is a hash Join in SQL? how to use it?

Answer:
This kind of join has two inputs like all the join algorithms, first is the build input i.e. outer table and the second is probe input i.e. inner table. The query optimizer allots the roles so that the smaller of the above two inputs is the build input. The Variant of hash join can do deduplication i.e. removal and grouping, such as Sum (col1) Group-By Id. These updates are used only for one input and for both the build and probe parts.
The below query is an example of a hash join: –

SELECT a. Name As AName, b. Name As BName
FROM P.Product p
JOIN P.ProductSub ps
ON p.ID = ps.ID
ORDER BY p.Name, ps. Name

 

 

6. How data should be structured to perform Join Operations in a one-to-many relationship and what about a many-to-many relationship?

Answer:
This one is a bit trickier and is an interesting database design question.
Generally, one-to-many relationships are structured using a single FOREIGNKEY. Consider our example of customers and orders above:

CREATE TABLE customers (
customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
);

CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_placed_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

This is a one-to-many relationship because one customer can place multiple orders, but one order cannot be assigned to more than one customer. As such, we’ve defined it with a simple foreign key in the orders table pointing to a given customer_id, and we can use JOIN clauses in our SELECT queries fairly easily.
Many-to-many relationships are a bit more complicated. For example, what if we had an orders table and a products table with a many-to-many relationship: any order can contain multiple products, and any product can be assigned to multiple orders. How would we structure our database?
The answer: we use an intermediary mapping table with two FOREIGN KEYs. Consider the following:

CREATE TABLE orders (
order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_placed_date DATE NOT NULL,
);

CREATE TABLE products (
product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price INT NOT NULL
);

CREATE TABLE products_to_orders (
product_to_order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

Above, we’ve created a separate table called products_to_orders that maps items on the products table to items on the orders table. Each row in our products_to_orders table represents one product-order combination so that multiple products can be assigned to one order — and a single product can be assigned to multiple orders.
In this example, we need to use two JOIN statements to link all these tables together: one to link products_to_orders to products, and one to link products_to_orders with orders.

 

 SQL Server Interview Questions regarding Joins output

Table A

Table B

Left Join Output

RIGHT JOIN

LEFT JOIN

FULL OUTER JOIN

SELF JOIN