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.
Creating three tables student, branch_details, credit_details with SQL query as follows:
CREATE TABLE student
CREATE TABLE branch_details
CREATE TABLE credit_details
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
INSERT INTO branch_details VALUES
INSERT INTO credit_details VALUES
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:
FROM table_1, table_2,...table_n
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.
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.
Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation
Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation