Mysql select from multiple tables with same fields

This article will be looking into queries to select from multiple tables through different ways for different requirements.

Table of Contents

• Select from multiple tables MySQL using JOINS
• Select from multiple tables MySQL using UNIONS
• Select from multiple tables MySQL using Subqueries
• Select from multiple tables MySQL with IN[]

Before moving into each section, let us see what all tables we will be using for demonstration.

We will be using four tables, student_details, student_registration_status, student_address_city and temporary_summer_students to illustrate how to get data from these tables. Taking a peek into all these tables

Select from multiple tables in MySQL using JOINS

In this section, we will see how to select columns from multiple tables using JOIN.

Advertisements

Example:- Get all the columns from student_details along with city and registration_flag.

Observe the below query for solution.

SELECT 
    s1.*, s2.registration_flag, s3.city
FROM
    student_details s1,
    student_registration_status s2,
    student_address_city s3
WHERE
    s1.student_id = s2.student_id
        AND s1.student_id = s3.student_id
ORDER BY s1.student_id;

Action Output Message Response :-

9 row[s] returned

Output:-

Explanation:-

• Here, we are fetching student_id, student_name, sex, and age from the student_details table.
• Fetching registration_flag from student_registration_status table.
• Fetching city from the student_address_city table.
• INNER JOIN is applied ON student_id for all the three tables to ensure that the right information is obtained for a particular student_id.

Select from multiple tables in MySQL using UNION

In this section, we will see how to select columns from multiple tables using Union.

Example:- Get the student_id and student_name for permanent as well as temporary summer students.

Observe the below query for the solution.

SELECT 
    student_id, student_name
FROM
    student_details 
UNION SELECT 
    student_id, student_name
FROM
    temporary_summer_students;

Action Output Message Response :-

17 row[s] returned

Output:-

Explanation:-

• Here, we are doing UNION between two tables, student_details, and temporary_summer_students for student_id and student_name.
• UNION will give the results vertically aligned.

Select from multiple tables in MySQL using SUBQUERIES

In this section, we will see how to select columns from multiple tables using subqueries.

Example:- Get the details and count of ‘A’ grades for students who have at least one ‘A’ grade.

Observe the below query for the solution.

SELECT 
    *
FROM
    student_details s1,
    [SELECT 
        student_id, COUNT[grade] AS count_gradeA 
    FROM
        students_grades
    WHERE
        grade = 'A'
    GROUP BY student_id] s2
WHERE
    s1.student_id = s2.student_id;

Action Output Message Response :-

5 row[s] returned

Output:-

Explanation:-

• The query is divided into two parts subquery and main query.
• The subquery gets the student_id and the count of ‘A’ grades for students from the students_grades table.
• The main query retrieves the details of student_id[s] retrieved from the subquery.

In this section, we will see how to select columns from multiple tables using IN[].

Example:- Get details of students who are not registered in the student_registration_status table.

Observe the below query for the solution.

SELECT 
    s1.*, s3.city
FROM
    student_details s1,
    student_address_city s3
WHERE
    s1.student_id NOT IN [SELECT 
            student_id
        FROM
            student_registration_status]
        AND s1.student_id = s3.student_id;

Action Output Message Response :-

3 row[s] returned

Output:-

Explanation:-

• There were only three students with student_id 3,8,11, which did not register in the student_registration_status table.
• The query is fetching student_id, student_name, sex, and age from the student_details table and city from the student_address_city table.
• WHERE clause with NOT IN[] ensures that we get the details of only those students who are not present in the student_registration_status table.

We hope this article helped to build an understanding of retrieving data from multiple tables in MySQL. Good Luck !!!