« Previous Next »

What are Joins?

MySQL Joins are used to retrieve data from multiple tables based on a related column between them. Joins are essential for relational databases.

MySQL Joins - Introduction

In MySQL, JOIN is used to combine rows from two or more tables based on a related column between them. It helps to retrieve meaningful data from multiple tables in a single query.

Types of MySQL Joins

  1. INNER JOIN
  2. LEFT JOIN (or LEFT OUTER JOIN)
  3. RIGHT JOIN (or RIGHT OUTER JOIN)
  4. FULL JOIN (Not supported directly in MySQL)

1. INNER JOIN

Returns records that have matching values in both tables.

SELECT * FROM students
INNER JOIN courses ON students.course_id = courses.id;

2. LEFT JOIN

Returns all records from the left table, and matched records from the right table. Unmatched records from the right table will be NULL.

SELECT * FROM students
LEFT JOIN courses ON students.course_id = courses.id;

3. RIGHT JOIN

Returns all records from the right table, and matched records from the left table. Unmatched records from the left table will be NULL.

SELECT * FROM students
RIGHT JOIN courses ON students.course_id = courses.id;

4. FULL JOIN

MySQL does not support FULL JOIN directly, but it can be achieved using a combination of LEFT and RIGHT JOIN with UNION.

SELECT * FROM students
LEFT JOIN courses ON students.course_id = courses.id
UNION
SELECT * FROM students
RIGHT JOIN courses ON students.course_id = courses.id;

Examples of Joints in MySQL
Create two tables
Table 1: Students

    CREATE TABLE students (
        student_id INT PRIMARY KEY AUTO_INCREMENT,
        student_name VARCHAR(100),
        student_course VARCHAR(100),
        admission_date DATE,
        contact_number VARCHAR(15),
        email VARCHAR(100),
        address TEXT
    );
Insert Query 
  INSERT INTO students 
  (student_name, student_course, admission_date, contact_number, email, address) 
  VALUES
  ('Amit Sharma', 'BCA', '2024-06-01', '9876543210', 'amit@gmail.com', 'Delhi'),
  ('Priya Verma', 'MCA', '2024-06-05', '9876543211', 'priya@gmail.com', 'Mumbai'),
  ('Ravi Kumar', 'B.Tech', '2024-06-07', '9876543212', 'ravi@gmail.com', 'Chennai'),
  ('Neha Singh', 'B.Sc', '2024-06-10', '9876543213', 'neha@gmail.com', 'Kolkata'),
  ('Rakesh Yadav', 'M.Tech', '2024-06-11', '9876543214', 'rakesh@gmail.com', 'Jaipur'),
  ('Simran Kaur', 'BCA', '2024-06-12', '9876543215', 'simran@gmail.com', 'Punjab'),
  ('Mohit Sinha', 'MCA', '2024-06-13', '9876543216', 'mohit@gmail.com', 'Bangalore'),
  ('Anjali Gupta', 'B.Com', '2024-06-14', '9876543217', 'anjali@gmail.com', 'Pune'),
  ('Suresh Das', 'MBA', '2024-06-15', '9876543218', 'suresh@gmail.com', 'Nagpur'),
  ('Kavita Mehra', 'BBA', '2024-06-16', '9876543219', 'kavita@gmail.com', 'Surat');
  ('Rahul Mehta', 'B.Sc IT', '2024-06-17', '9876543220', 'rahul@gmail.com', 'Lucknow'),
  ('Divya Joshi', 'M.Com', '2024-06-18', '9876543221', 'divya@gmail.com', 'Indore'),
  ('Arjun Rathi', 'BBA', '2024-06-19', '9876543222', 'arjun@gmail.com', 'Hyderabad'),
  ('Sneha Kapoor', 'MBA', '2024-06-20', '9876543223', 'sneha@gmail.com', 'Ahmedabad'),
  ('Deepak Rawat', 'M.Tech', '2024-06-21', '9876543224', 'deepak@gmail.com', 'Bhopal');
Table 2: Fees
    
    CREATE TABLE fees (
        fees_id INT PRIMARY KEY AUTO_INCREMENT,
        student_id INT,
        total_fees DECIMAL(10,2),
        paid_fees DECIMAL(10,2),
        pending_fees DECIMAL(10,2),
        last_payment_date DATE,
        due_date DATE,
        status ENUM('Paid', 'Pending', 'Overdue'),
        FOREIGN KEY (student_id) REFERENCES students(student_id)
    );
Insert Query
  INSERT INTO fees 
  (student_id, total_fees, paid_fees, pending_fees, last_payment_date, due_date, status) 
  VALUES
  (5, 45000.00, 20000.00, 25000.00, '2024-06-30' , '2024-07-30', 'Overdue'),
  (6, 47000.00, 27000.00, 20000.00, '2024-07-01' , '2024-08-01', 'Pending'),
  (7, 52000.00, 52000.00,     0.00, '2024-07-03' , '2024-08-03', 'Paid'),
  (8, 58000.00, 30000.00, 28000.00, '2024-07-05' , '2024-08-05', 'Pending'),
  (9, 61000.00, 40000.00, 21000.00, '2024-07-07' , '2024-08-07', 'Pending'),
  (10, 45000.00, 25000.00, 20000.00, '2024-07-09', '2024-08-09', 'Overdue'),
  (11, 49000.00, 49000.00,     0.00, '2024-07-10', '2024-08-10', 'Paid'),
  (12, 51000.00, 30000.00, 21000.00, '2024-07-11', '2024-08-11', 'Pending'),
  (13, 53000.00, 25000.00, 28000.00, '2024-07-12', '2024-08-12', 'Overdue'),
  (14, 60000.00, 60000.00,     0.00, '2024-07-13', '2024-08-13', 'Paid'),
  (15, 48000.00, 20000.00, 28000.00, '2024-07-14', '2024-08-14', 'Pending');
« Previous Next »