The previous Lesson of MySql Lessons Series was about MySql Delete Query and in this lesson will learn about MySql Joins.
A join is used in mysql to fetch rows from more than one tables in a single query to produce a common result. Let us say we have 2 tables in our database, named student and marks. The details of the students are stored in the student table and their marks are saved in the marks table i.e
The above images shows the records and marks of the students in student and marks tables respectively. If we want to show the details of a students and marks obtained by him running a single query then the need for joins in mysql arises. There are 3 types of joins in mysql:
1. Inner Join
2. Left Join
3. Right Join
We can also write the above "INNER JOIN" query with only "JOIN". The result of both INNER JOIN and JOIN will be same.
MySql Inner Join between the student and marks tables is given below:
In the first part of the above query before "FROM student", we have selected the column names we need from both tables along with the names of their respective tables and then in the second part of the query after "FROM student", we have joined the table marks with the table student with the join on the student id of the marks table and id of the student table.
Another method of writing mysql inner join query is given below:
The only change in the above query is that we are using the abbreviations st for student table and m for marks table instead of writing the complete table name. The result of the above queries is shown in the image below:
MySql Inner Join fetches only those rows which are common between the joining tables i.e those ids in student table that have matching studentids in marks table.
We can also write the above "LEFT JOIN" query with "LEFT OUTER JOIN". The result of both LEFT JOIN and LEFT OUTER JOIN will be same.
MySql Left Join between the student and marks tables is given below:
Another method of writing mysql left join query is given below:
The result of the above queries is shown in the image below:
MySql Left Join fetches all rows from left table (student) and the matching rows from right table (marks) i.e all ids from student table and those studentids from marks table that match ids from student table.
We can also write the above "RIGHT JOIN" query with "RIGHT OUTER JOIN". The result of both RIGHT JOIN and RIGHT OUTER JOIN will be same.
MySql Right Join between the student and marks tables is given below:
Another method of writing mysql right join query is given below:
The result of the above queries is shown in the image below:
MySql Right Join fetches all rows from the right table (marks) and the matching rows from the left table (student) i.e all studentids from marks table and those ids from student table that match studentids from marks table.
A join is used in mysql to fetch rows from more than one tables in a single query to produce a common result. Let us say we have 2 tables in our database, named student and marks. The details of the students are stored in the student table and their marks are saved in the marks table i.e
The above images shows the records and marks of the students in student and marks tables respectively. If we want to show the details of a students and marks obtained by him running a single query then the need for joins in mysql arises. There are 3 types of joins in mysql:
1. Inner Join
2. Left Join
3. Right Join
Inner Join in MySql:
The basic syntax of MySql Inner Join is as follows:
SELECT table1.column names,table2.column names FROM table1
INNER JOIN table2 ON table1.column name = table2.column name
We can also write the above "INNER JOIN" query with only "JOIN". The result of both INNER JOIN and JOIN will be same.
SELECT table1.column names,table2.column names FROM table1
JOIN table2 ON table1.column name = table2.column name
MySql Inner Join between the student and marks tables is given below:
SELECT student.id,student.Name,student.Standard,
marks.English,marks.Science,marks.Maths,
marks.Philosophy FROM student
INNER JOIN marks ON student.id = marks.studentid
In the first part of the above query before "FROM student", we have selected the column names we need from both tables along with the names of their respective tables and then in the second part of the query after "FROM student", we have joined the table marks with the table student with the join on the student id of the marks table and id of the student table.
Another method of writing mysql inner join query is given below:
SELECT st.id,st.Name,st.Standard,m.English,
m.Science,m.Maths,m.Philosophy FROM student st
INNER JOIN marks m ON st.id = m.studentid
The only change in the above query is that we are using the abbreviations st for student table and m for marks table instead of writing the complete table name. The result of the above queries is shown in the image below:
MySql Inner Join fetches only those rows which are common between the joining tables i.e those ids in student table that have matching studentids in marks table.
Left Join in MySql:
The basic syntax of MySql Left Join is as follows:
SELECT table1.column names,table2.column names FROM table1
LEFT JOIN table2 ON table1.column name = table2.column name
We can also write the above "LEFT JOIN" query with "LEFT OUTER JOIN". The result of both LEFT JOIN and LEFT OUTER JOIN will be same.
SELECT table1.column names,table2.column names FROM table1
LEFT OUTER JOIN table2 ON table1.column name = table2.column name
MySql Left Join between the student and marks tables is given below:
SELECT student.id,student.Name,student.Standard,marks.English,
marks.Science,marks.Maths,marks.Philosophy FROM student
LEFT JOIN marks ON student.id = marks.studentid
Another method of writing mysql left join query is given below:
SELECT st.id,st.Name,st.Standard,m.English,m.Science,
m.Maths,m.Philosophy FROM student st
LEFT JOIN marks m ON st.id = m.studentid
The result of the above queries is shown in the image below:
MySql Left Join fetches all rows from left table (student) and the matching rows from right table (marks) i.e all ids from student table and those studentids from marks table that match ids from student table.
Right Join in MySql:
The basic syntax of MySql Right Join is as follows:
SELECT table1.column names,table2.column names FROM table1
RIGHT JOIN table2 ON table1.column name = table2.column name
We can also write the above "RIGHT JOIN" query with "RIGHT OUTER JOIN". The result of both RIGHT JOIN and RIGHT OUTER JOIN will be same.
SELECT table1.column names,table2.column names FROM table1
RIGHT OUTER JOIN table2 ON
table1.column name = table2.column name
MySql Right Join between the student and marks tables is given below:
SELECT student.id,student.Name,student.Standard,marks.English,
marks.Science,marks.Maths,marks.Philosophy FROM student
RIGHT JOIN marks ON student.id = marks.studentid
Another method of writing mysql right join query is given below:
SELECT st.id,st.Name,st.Standard,m.English,m.Science,m.Maths,
m.Philosophy FROM student st RIGHT JOIN marks m ON
st.id = m.studentid
The result of the above queries is shown in the image below:
MySql Right Join fetches all rows from the right table (marks) and the matching rows from the left table (student) i.e all studentids from marks table and those ids from student table that match studentids from marks table.
No comments:
Post a Comment