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
How to use join in mysql?
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
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();
File Name :
File Name :
File Name :