Most Popular Tutorials




what is join?

SQL joins are used to combine rows from two or more tables. MySQL JOINS are used to retrieve data from multiple tables.

Types of SQL join.

The are two types of SQL JOINS - EQUI JOIN and NON EQUI JOIN

1. SQL EQUI JOIN :

The SQL EQUI JOIN is a simple sql join uses the equal sign(=) as the comparison operator for the condition. It has two types - SQL Outer join and SQL Inner join.

2. SQL NON EQUI JOIN :

The SQL NON EQUI JOIN is a join uses comparison operator other than the equal sign like >, <, >=, <= with the condition.

SQL EQUI JOIN : INNER JOIN and OUTER JOIN.

1. SQL INNER JOIN
This type of EQUI JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.


2. SQL OUTER JOIN
This type of EQUI JOIN returns all rows from one table and only those rows from the secondary table where the joined condition is satisfying i.e. the columns are equal in both tables.
In order to perform a JOIN query, the required information we need are:
a) The name of the tables 
b) Name of the columns of two or more tables, based on which a condition will perform.

What is Equi Join in SQL?

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

You may also perform EQUI JOIN by using JOIN keyword followed by ON keyword and then specifying names of the columns along with their associated tables to check equality.
Syntax

SELECT column_list FROM table1, table2.... WHERE table1.column_name = table2.column_name;

File name : index.php

<?php
include 'db.php';
$query = "SELECT agents.agent_name,customer.cust_name,
customer.cust_city
FROM agents,customer
WHERE agents.working_area=customer.cust_city";
$result = mysqli_query($con,$query) or die(mysqli_error($con));
while($row = mysqli_fetch_array($result))
{
echo $row['agent_name']. " - ". $row['cust_name'] ." - ". $row['cust_city'];
echo "<br />";
}
?>

What is the difference between Equi Join and Inner Join in SQL?

An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.

An inner join is a join of two or more tables that returns only those rows (compared using a comparison operator) that satisfy the join condition.


jointable

File name : index.php

Example of equi join without primary key:-
<?php
include 'db.php';
$query = "SELECT agents.agent_code,agents.agent_name, customer.customer_code,customer_name
FROM agents,customer WHERE agents.agent_code = customer.agent_code";
$result = mysqli_query($con,$query) or die(mysqli_error($con));
while($row = mysqli_fetch_array($result)){
echo $row['agent_code']. " - ". $row['agent_name']. " - ". $row['customer_code']. " - ". $row['customer_name'];
echo "<br />";
}
?>

SQL non Equi Join

The SQL NON EQUI JOIN uses comparison operator instead of the equal sign like >, <, >=, <= along with conditions.

Syntax
SELECT * FROM table_name1, table_name2 WHERE table_name1.column [> | < | >= | <= ] table_name2.column;


To get 'ord_num' and 'ord_amount' columns from 'orders' table aliased as 'a' and 'cust_name' and 'working_area' columns from 'customer' table aliased as 'b' after joining said two tables with following condition - 1. 'ord_amount' of 'orders' table matches any of the 'opening_amt' of 'customer' table.

the following sql statement can be used : 2. SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area FROM orders a,customer b WHERE a.ord_amount BETWEEN b.opening_amt AND b.opening_amt;

File name : index.php

<?php
include 'db.php';
$query = "SELECT a.ord_num,a.ord_amount,b.cust_name,b.working_area
FROM orders a,customer b
WHERE a.ord_amount
BETWEEN b.opening_amt AND b.opening_amt;";
$result = mysqli_query($con,$query) or die(mysqli_error($con));
while($row = mysqli_fetch_array($result))
{
echo $row['ord_num']. " - ". $row['ord_amount'] ." - ". $row['cust_name']. " - ". $row['working_area'];
echo "<br />";
}
?>

SQL INNER JOIN

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

Syntax
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; OR SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

To join 'item_name', 'item_unit' columns from 'foods' table and 'company_name', 'company_city' columns from 'company' table, with following condition -

1. 'company_id' of 'foods' and 'company' table must be same, the following sql statement can be used :
SELECT foods.item_name, foods.item_unit, company.company_name, company.company_city FROM foods INNER JOIN company ON foods.company_id =company.company_id;

File name : index.php

<?php
include 'db.php';
$query = "SELECT company_product.item_name,company_product.item_unit,
company.company_name,company.company_city
FROM company_product
INNER JOIN company
ON company_product.company_id =company.company_id;";
$result = mysqli_query($con,$query) or die(mysqli_error($con));
while($row = mysqli_fetch_array($result))
{
echo $row['item_name']. " - ". $row['item_unit']. " - ". $row['company_name']. " - ". $row['company_city'];
echo "<br />";
}
?>

Example of SQL INNER JOIN using JOIN keyword

To get 'item_name', 'item_unit' columns from 'foods' table and 'company_name', 'company_city' columns from 'company' table, after joining these mentioned tables, with following condition -
1. 'company_id' of 'foods' and 'company_id' of 'company' table must be same, the following sql statement can be used :
SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city FROM foods JOIN company ON foods.company_id =company.company_id;


Difference between JOIN and INNER JOIN

JOIN returns all rows from tables where the key record of one table is equal to the key records of another table. The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection. Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.


Using JOIN Clause
SELECT * FROM Table1 JOIN Table2 ON Table1.column_name=Table2.column_name;
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.column_name= Table2.column_name;

SQL INNER JOIN (simple join).

An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

Returns records that have matching values in both tables


inner join

File name : index.php

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

File name : index.php

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;


File name : index.php

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Left Join :-

Returns all records from the left table, and the matched records from the right table

left join

left join

Note: The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

File name : index.php

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Right Join

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

Returns all records from the right table, and the matched records from the left table

File name : index.php


right join

right join

File name : index.php

SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

Full outer join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

Returns all records when there is a match in either left or right table

full outer join

File name : index.php

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;


File name : index.php

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

    • INNER JOIN :-

    The inner JOIN is used to return rows from both tables that satisfy the given condition.

    File Name :

    SELECT emp.first_name, emp.last_name, u.user_type, u.username FROM employee AS emp
    INNER JOIN user AS u ON emp.id = u.employee_id;

    Table Order in Inner Joins :-

    inner joins only return matching rows if the condition is true. it doesn’t matter which table you specify as the first table. Following both queries return same result.

    File Name :

    SELECT emp.first_name, emp.last_name, u.user_type, u.username FROM `employee` AS emp
    INNER JOIN `user` AS u ON emp.id = u.employee_id;


    SELECT emp.first_name, emp.last_name, u.user_type, u.username FROM `user` AS u
    INNER JOIN `employee` AS emp ON emp.id = u.employee_id;


    File Name :

    SELECT members.`first_name` , members.`last_name` , movies.`title`
    FROM members ,movies WHERE movies.`id` = members.`movie_id`

    Table 1 :- movies

    File Name :

    CREATE TABLE `movies` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    PRIMARY KEY (`id`) USING BTREE
    );

    table 2 :- members

    File Name :

    CREATE TABLE `members` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `movies_id` INT(10) NULL DEFAULT NULL,
    `first_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `last_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `FK_members_movies` (`movies_id`) USING BTREE,
    CONSTRAINT `FK_members_movies` FOREIGN KEY (`movies_id`) REFERENCES `test`.`movies` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
    );

    Inner join example

    File Name :

    SELECT members.id AS member_id, members.first_name, movies.title FROM test.`members` INNER JOIN test.movies ON members.movies_id = movies.id;

    output :-


    LEFT JOIN

    The LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. Where no matches have been found in the table on the right, NULL is returned.

    Left joins returns all the rows from first table (left table) for specified relationship and fetch only the matching ones from second table (right table).

    File Name :

    SELECT emp.first_name, emp.last_name, u.user_type, u.username FROM `employee` AS emp
    LEFT JOIN `user` AS u ON emp.id = u.employee_id;

    First MySQL looks at the table relationship and identifies `id` field of `employee` table as the left part and `employee_id` field of `user` table as the right part. It then selects all the rows from `employee` table that have values for `id` column. In the second step, based on e.id = u.employee_id relationship, it fetches matching rows from `user` table and creates final rows set (These rows contain values from both `employee` and `user` tables). If it can’t find a matching row, NULL is set to the cells of `user` table.

    File Name :

    SELECT e.first_name, e.last_name, u.user_type, u.username FROM `employee` AS e
    LEFT JOIN `user` AS u ON e.id = u.employee_id WHERE u.employee_id IS NOT NULL;

    File Name :

    SELECT A.title , B.first_name , B.last_name
    FROM movies AS A
    LEFT JOIN members AS B
    ON B.movie_id = A.id

    Left join example:

    SELECT movies.id,movies.title,members.id AS member_id, members.first_name FROM movies LEFT JOIN members ON members.movies_id = movies.id;


    RIGHT JOIN

    The RIGHT JOIN returns all the columns from the table on the right even if no matching rows have been found in the table on the left. Where no matches have been found in the table on the left, NULL is returned.

    priority is given to right table and fetches all the rows from right table for given relationship.

    Following query returns all the rows from `user` table and fills NULL for `employee` table’s cells if no matching row found.

    File Name :

    SELECT emp.first_name, emp.last_name, u.user_type, u.username FROM `employee` AS emp
    RIGHT JOIN `user` AS u ON u.employee_id = emp.id;

    SELECT movies.id,movies.title,members.id AS member_id, members.first_name FROM movies RIGHT JOIN members ON movies.id = members.movies_id;

    Right join example:


    MySql Join

  • INNER JOIN: Returns records that have matching values in both tables
  • LEFT JOIN: Returns all records from the left table, and the matched records from the right table
  • RIGHT JOIN: Returns all records frohe right table, and the matched records from the left table
  • CROSS JOIN: Returns all records from both tables


    • Specifying Multiple Relationships in ON Clause

    Some data tables can have composite primary keys. That is, more than one column forms the primary key. In such cases, you can specify multiple relationships in ON clause using AND operator as shown in following syntax.

    File Name :

    SELECT t1.*, t2.* FROM table1 AS t1
    LEFT JOIN table2 AS t2 ON t1.field1 = t2.field1 AND t1.field2 = t2.field2;

    Using ORDER BY and LIMIT

    File Name :

    SELECT e.first_name, e.last_name, t.type, t.no FROM `employee` AS e
    LEFT JOIN `telephone` AS t ON e.id = t.employee_id ORDER BY t.type DESC, e.last_name ASC, e.first_name ASC LIMIT 4;

    Joining Multiple Tables

    File Name :

    SELECT e.first_name, e.last_name, u.user_type, u.username AS u_username, m.username AS m_username FROM `employee` AS e
    LEFT JOIN `user` AS u ON e.id = u.employee_id LEFT JOIN `meeting_user` AS m ON u.id = m.user_id;

    Join with condition

    $sql = SELECT `Table1`.*, `Table2`.* FROM `Table1`
    JOIN `Table2` ON `Table2`.`id` = `Table1`.`id`
    WHERE `Table1`.`field_name` = 'field_value'
    $query = $this->db->query($sql);
    return $rows = $query->result_array();

    $sql = "Select u.id,u.name,pi.image,p.post,f.id as fid from users
    join profile_image as pi on profile_image.user_id = users.id
    left join city as c on c.user_id = u.id
    left join post as p on p.user_id = u.id
    left join friends as f on f.user_id = u.id
    where u.id = $id";
    $query = $this->db->query($sql);
    return $rows = $query->result_array();





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link

    Ittutorial