The SQL joins used to combine records from two or more tables in a database. A join is a means for combining fields from two tables by using values common to each. There are different types of joins available in SQL like as self-join, inner-join, left-join, and right-join.
Self-join: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
Inner-join: INNER JOIN return all rows from multiple tables where the join condition is match.
Left-join: The LEFT JOIN keyword returns all the rows from the left table (tbl_T), even if there are no matches in the right table (tbl_T1).
Right-join: returns all rows from the right table, even if there are no matches in the left table.
Consider the following two tables.
Table : tbl_T
Table : tbl_T1
Example of Self Join:
Query :
- SELECT a.user_T_id, a.user_T_name, b.Ph_no
- FROM tbl_T a, tbl_T1 b where a.user_T_id=b.id
Result:
Example of Left-Join :
Query:
- SELECT a.user_T_name,a.user_T_pass,a.user_T_add,
- b.Ph_no,b.create_date
- FROM tbl_T a
- LEFT JOIN tbl_T1 b
- ON a.user_T_id = b.id;
Result:
Example of Inner-join:
Query:
- SELECT a.user_T_name,a.user_T_pass,a.user_T_add,
- b.Ph_no,b.create_date
- FROM tbl_T a
- INNER JOIN tbl_T1 b
- ON a.user_T_id = b.id;
Result:
Example of Right-join:
Query:
- SELECT a.user_T_name,a.user_T_pass,a.user_T_add,
- b.Ph_no,b.create_date
- FROM tbl_T a
- RIGHT JOIN tbl_T1 b
- ON a.user_T_id = b.id;
Result:
Thanks