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
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
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
SAMPLE EXAMPLE WITH OUR TABLE
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
SAMPLE EXAMPLE WITH OUR TABLE
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
SAMPLE EXAMPLE FROM OUR TABLE
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
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
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
This was helpful. :)
ReplyDelete