MySQL Joins: Definition, Types, Syntax, and Examples

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

Then, look at a selection from the "Course" table:

course_id	course_name
101	Math
102	Science
103	History

Notice that the "course_id" column in the "Course" table refers to the "course_id" in the "Students" table. The relationship between the two tables above is the "course_id" column.

Then, we can create the following SQL statement (that contains an INNER JOIN), that selects records that have matching values in both tables:

Example

SELECT students.name, courses.course_name

FROM students

INNER JOIN courses

ON students.course_id = courses.course_id;

Try it yourself

and it will produce something like this:

name	course_name
Alice	Math
Charlie	Science
David	Math
Eve	History
Frank	History
Alice	Science

Types of Joins in MySQL

INNER JOIN → Returns records with matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN) → Returns all records from the left table and matched records from the right table.
RIGHT JOIN (RIGHT OUTER JOIN) → Returns all records from the right table and matched records from the left table.
CROSS JOIN → Returns the Cartesian product of both tables (every combination of rows).
SELF JOIN → Joins a table to itself using table aliases.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.