PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
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.
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
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
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
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
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
File name : index.php
https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins