SQL Joins Tutorial – INNER, LEFT, RIGHT, FULL, and CROSS JOIN

SQL Joins Tutorial – INNER, OUTER, and CROSS JOIN

In SQL, Joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve meaningful information by connecting tables.

1. INNER JOIN

Definition: Returns only the rows that have matching values in both tables.

SELECT students.name, departments.dept_name
FROM students
INNER JOIN departments
ON students.dept_id = departments.dept_id;


2. LEFT OUTER JOIN

Definition: Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right table are NULL.

SELECT students.name, departments.dept_name
FROM students
LEFT JOIN departments
ON students.dept_id = departments.dept_id;


3. RIGHT OUTER JOIN

Definition: Returns all rows from the right table and matching rows from the left table. Non-matching rows from the left table are NULL.

SELECT students.name, departments.dept_name
FROM students
RIGHT JOIN departments
ON students.dept_id = departments.dept_id;


4. FULL OUTER JOIN

Definition: Returns all rows when there is a match in either left or right table. Non-matching rows are filled with NULLs.

SELECT students.name, departments.dept_name
FROM students
FULL OUTER JOIN departments
ON students.dept_id = departments.dept_id;


5. CROSS JOIN

Definition: Returns the Cartesian product of the two tables. All possible combinations of rows are returned.

SELECT students.name, departments.dept_name
FROM students
CROSS JOIN departments;


Lab Practice Tasks

  1. Create students and departments tables with sample data.
  2. Write INNER JOIN query to fetch student names with their department names.
  3. Experiment with LEFT, RIGHT, and FULL OUTER JOINs and observe NULL values.
  4. Perform a CROSS JOIN and count total combinations.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)