{"id":3996,"date":"2022-02-09T10:34:31","date_gmt":"2022-02-09T18:34:31","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3996"},"modified":"2022-02-09T10:34:33","modified_gmt":"2022-02-09T18:34:33","slug":"what-is-the-difference-between-inner-join-left-join-right-join-and-full-join","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3996","title":{"rendered":"What is the Difference Between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?"},"content":{"rendered":"\n
\"alt<\/figure>\n\n\n\n

There are different types of joins available in SQL:<\/p>\n\n\n\n

INNER JOIN<\/strong>: returns rows when there is a match in both tables.<\/p>\n\n\n\n

LEFT JOIN<\/strong>: returns all rows from the left table, even if there are no matches in the right table.<\/p>\n\n\n\n

RIGHT JOIN<\/strong>: returns all rows from the right table, even if there are no matches in the left table.<\/p>\n\n\n\n

FULL JOIN<\/strong>: combines the results of both left and right outer joins.<\/p>\n\n\n\n

The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.<\/p>\n\n\n\n

SELF JOIN<\/strong>: joins a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.<\/p>\n\n\n\n

CARTESIAN JOIN<\/strong>: returns the Cartesian product of the sets of records from the two or more joined tables.<\/p>\n\n\n\n

We can take each first four joins in Details :<\/p>\n\n\n\n

We have two tables with the following values.<\/p>\n\n\n\n

TableA<\/strong><\/p>\n\n\n\n

id  firstName                  lastName\n.......................................\n1   arun                        prasanth                 \n2   ann                         antony                   \n3   sruthy                      abc                      \n6   new                         abc                                           \n<\/code><\/pre>\n\n\n\n
TableB<\/strong><\/p>\n\n\n\n
id2 age Place\n................\n1   24  kerala\n2   24  usa\n3   25  ekm\n5   24  chennai\n<\/code><\/pre>\n\n\n\n
…………………………………………………………..<\/p>\n\n\n\n
INNER JOIN<\/strong><\/p>\n\n\n\n
Note<\/strong> : gives the intersection of the two tables, i.e. rows TableA and TableB have in common.<\/p>\n\n\n\n
Syntax<\/p>\n\n\n\n
SELECT table1.column1, table2.column2...\n  FROM table1\n INNER JOIN table2\n    ON table1.common_field = table2.common_field;\n<\/code><\/pre>\n\n\n\n
Apply it in our sample table :<\/p>\n\n\n\n
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place\n  FROM TableA\n INNER JOIN TableB\n    ON TableA.id = TableB.id2;\n<\/code><\/pre>\n\n\n\n
Result<\/p>\n\n\n\n
firstName       lastName       age  Place\n..............................................\narun            prasanth        24  kerala\nann             antony          24  usa\nsruthy          abc             25  ekm\n<\/code><\/pre>\n\n\n\n
LEFT JOIN<\/strong><\/p>\n\n\n\n
Note<\/strong> : gives all selected rows in TableA, plus any common selected rows in TableB.<\/p>\n\n\n\n
Syntax<\/p>\n\n\n\n
SELECT table1.column1, table2.column2...\n  FROM table1\n  LEFT JOIN table2\n    ON table1.common_field = table2.common_field;\n<\/code><\/pre>\n\n\n\n
Apply it in our sample table :<\/p>\n\n\n\n
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place\n  FROM TableA\n  LEFT JOIN TableB\n    ON TableA.id = TableB.id2;\n<\/code><\/pre>\n\n\n\n
Result<\/p>\n\n\n\n
firstName                   lastName                    age   Place\n...............................................................................\narun                        prasanth                    24    kerala\nann                         antony                      24    usa\nsruthy                      abc                         25    ekm\nnew                         abc                         NULL  NULL\n<\/code><\/pre>\n\n\n\n
RIGHT JOIN<\/strong><\/p>\n\n\n\n
Note<\/strong> : gives all selected rows in TableB, plus any common selected rows in TableA.<\/p>\n\n\n\n
Syntax<\/p>\n\n\n\n
SELECT table1.column1, table2.column2...\n  FROM table1\n RIGHT JOIN table2\n    ON table1.common_field = table2.common_field;\n<\/code><\/pre>\n\n\n\n
Apply it in our sample table :<\/p>\n\n\n\n
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place\n  FROM TableA\n RIGHT JOIN TableB\n    ON TableA.id = TableB.id2;\n<\/code><\/pre>\n\n\n\n
Result<\/p>\n\n\n\n
firstName                   lastName                    age     Place\n...............................................................................\narun                        prasanth                    24     kerala\nann                         antony                      24     usa\nsruthy                      abc                         25     ekm\nNULL                        NULL                        24     chennai\n<\/code><\/pre>\n\n\n\n
FULL JOIN<\/strong><\/p>\n\n\n\n
Note<\/strong> : returns all selected values from both tables.<\/p>\n\n\n\n
Syntax<\/p>\n\n\n\n
SELECT table1.column1, table2.column2...\n  FROM table1\n  FULL JOIN table2\n    ON table1.common_field = table2.common_field;\n<\/code><\/pre>\n\n\n\n
Apply it in our sample table :<\/p>\n\n\n\n
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place\n  FROM TableA\n  FULL JOIN TableB\n    ON TableA.id = TableB.id2;\n<\/code><\/pre>\n\n\n\n
Result<\/p>\n\n\n\n
firstName                   lastName                    age    Place\n...............................................................................\narun                        prasanth                    24    kerala\nann                         antony                      24    usa\nsruthy                      abc                         25    ekm\nnew                         abc                         NULL  NULL\nNULL                        NULL                        24    chennai\n<\/code><\/pre>\n\n\n\n
Interesting Fact<\/strong><\/p>\n\n\n\n