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

16 March 2016

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

Join query image inner join left join right join
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

          This will returns both table matches where the query is matching.
          This will returns left table all data with right table matches.

          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 structure
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 structure
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 structure image

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
Inner join structure image results

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 structure image

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
Left join structure image results

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 structure image

RIGHT JOIN QUERY SYNTAX

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

Right join structure image results


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

2 comments:

^