SQL – SELECT from Multiple Tables with MS SQL Server

In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n rows then the resulting table after selecting two tables i.e cross join of two tables contains m*n rows.

Let us see how to select multiple tables using the MSSQL server:
 

Creating a database :

Creating a database GeeksForGeeks by using the following SQL query as follows.

CREATE DATABASE GeeksForGeeks;

Using the database :

Using the database student using the following SQL query as follows.

USE GeeksForGeeks;

Creating Tables:

Creating three tables student, branch_details, credit_details  with SQL query as follows:

CREATE TABLE student
(  
stu_id varchar(10),
stu_name varchar(20),
branch varchar(20)
);

CREATE TABLE branch_details
(  
branch_name varchar(10),
subjects INT
);

CREATE TABLE credit_details
(  
branch varchar(20), 
max_credits INT,
min_credits_required INT
);

Verifying the database :

To view the description of the three tables in the database GeeksForGeeks using the following SQL query as follows.

EXEC sp_columns student;
EXEC sp_columns branch_details;
EXEC sp_columns credit_details;

Inserting data into the three tables:

Inserting rows into tables using the following SQL query as follows:

INSERT INTO student VALUES
('1901401','DEVA','C.S'),
('1901402','HARSH','C.S'),
('1901403','DAVID','E.C'),
('1901404','GAURAV','E.C');

INSERT INTO branch_details VALUES
('C.S',8),
('E.C',7),
('M.E',7),
('I.C.E',9),
('E.E.E',8);

INSERT INTO credit_details VALUES
('C.S',24, 12),
('E.C',21, 11),
('M.E',21, 11),
('I.C.E',27,14),
('E.E.E',24,12);

Verifying the inserted data :

Viewing the three tables after inserting rows by using the following SQL query as follows.

SELECT * FROM student;
SELECT * FROM branch_details;
SELECT * FROM credit_details;

Using SELECT statement for n tables:

SYNTAX:
SELECT columns
FROM table_1, table_2,...table_n
WHERE condition; 

Using SELECT statements for 2 tables student, branch_details:

SELECT * FROM student, branch_details;

This table contains 4*5 = 20 rows.

Using SELECT statements for 3 tables student, branch_details, credit_details:

SELECT * FROM student, branch_details, credit_details;

The resulting table contains 5*4*5 = 100 rows.

We can obtain other types of join by selecting multiple tables and mentioning appropriate condition in the WHERE clause but instead of using the SELECT with multiple tables and adding conditions using the keywords of joins is more optimal.

Example:

Query to display students who have enrolled in a course and their particulars.

SELECT student.stu_id, student.stu_name,
student.branch, subjects, max_credits
FROM student, branch_details, credit_details
WHERE student.branch = branch_details.branch_name AND 
branch_details.branch_name = credit_details.branch;

Note: When there are columns with the same name in different tables it is good to dot operator to point to the particular tables data.