Major three SQL join queries. inner join, left join, right join syntax and brief

Join queries are most important things in query writing, this SQL query only inter joining the separate table values through SQL query. here we are going to join to tables with this SQL query. like that we can join more tables also, here i'm going to show syntax and small example for all join queries. let see the query below. For UNION Query refer this link

This SQL Clause is mainly used to join one or more table in single query. here also im going to join two tables.

SQL JOIN TYPES

• INNER JOIN

          This will returns both table matches where the query is matching.

• LEFT JOIN

          This will returns left table all data with right table matches.

• RIGHT JOIN

          This will returns right table all data with left table matches.

• FULL JOIN

         This will returns if any one table matches also. not runs in mysql and mysqli, we can use union.

• SELF JOIN

          Temporarily renaming at least one table sql statement.

• CARTESIAN JOIN

          This will returns cartesian product of the set of  records from the two or more joined tables.

Here we are going to see only three join clause query only, they are INNER JOIN, LEFT JOIN, RIGHT JOIN.

TABLE ONE

table name - students

TABLE QUERY

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `r_number` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TABLE TWO

table name - marks

TABLE QUERY

CREATE TABLE `marks` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `language` int(11) NOT NULL,
  `english` int(11) NOT NULL,
  `maths` int(11) NOT NULL,
  `science` int(11) NOT NULL,
  `s_science` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INNER JOIN

Most of them using this SQL query only, why because this will fetch only matches data only.

INNER JOIN SYNTAX

SELECT table1.*, table2.*
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field

SAMPLE EXAMPLE WITH OUR TABLE

SELECT stu.*,mark.* 
FROM student as stu
INNER JOIN marks as mark
ON stu.id = mark.student_id

RESULT FOR INNER JOIN

LEFT JOIN
Left join will show all the left side table and if that related columns are empty means it will show null value in the result.

LEFT JOIN SYNTAX

SELECT table1.*, table2.*
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field

SAMPLE EXAMPLE WITH OUR TABLE

SELECT stu.*,mark.* 
FROM student as stu
LEFT JOIN marks as mark
ON stu.id = mark.student_id

RESULT FOR LEFT JOIN

RIGHT JOIN
Same life left join only, this will take all right side table fields, if left side tale is empty that will be null value.

RIGHT JOIN QUERY SYNTAX

SELECT table1.*, table2.*
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field

SAMPLE EXAMPLE FROM OUR TABLE

SELECT stu.*,mark.* 
FROM student as stu
RIGHT JOIN marks as mark
ON stu.id = mark.student_id

RESULTS FOR RIGHT JOIN

I hope this post is really helpful to you all. for union query in SQL. Thank you.


RELATED POSTS :
Basic of php and mysql ( insert coding )
Comment system using php and MYSQL
File download coding using PHP and MySql
Fetch two different table data in single page using PHP and MySql
Basic insert, view, edit, delete and update using PHP and Mysql
Get support with foreign and regional language in php and mysql
Activate and deactivate concept using php and mysql
Facebook style see more text onclick using php, mysql & javascript