SQL Basics Part6

HANA SQL JOINs.

 

 

HANA SQL provides below join types.

• CROSS JOIN
• INNER JOIN
• OUTER JOIN
  •  LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

CROSS JOIN– Its Cartesian product of two tables.Each row of the left table is connected to each row of the right table and there is no CROSS JOIN join condition.

 

---

Implicit Cross Join :

SELECT Column1, Column2, Column3
                FROM Table1, Table2
                WHERE Condition;

Explicit Cross Join:

SELECT Column1, Column2, Column3
                FROM Table1 CROSS JOIN Table Table2
                WHERE Condition;

---

INNER JOIN– One row of the left table and one row of the right table are always joined to a common result row – provided that the JOIN condition is fulfilled. JOIN Condition: L.X = R.Y

---

Implicit INNER JOIN:

SELECT Column1, Column2, Column3
                FROM Table1, Table2
               WHERE JOIN_Condition AND Supplementary_Condition;

Explicit INNER JOIN:

SELECT Column1, Column2, Column3
                FROM Table1 JOIN Tabl2e ON JOIN_Condition
                WHERE Supplementary_Condition;

Instead of JOIN simple we can write INNER JOIN.

---

OUTER JOIN–

• LEFT OUTER JOIN

One row of a table and one row of another table are always connected to a common result row – provided the JOIN condition is fulfilled.In addition, rows of the left table without matching row in the right table are copied to the query result. The missing values (from the right table) are filled with NULL values.

SELECT Column1, Column2, Column3
               FROM Table1 LEFT OUTER JOIN Table2 ON JOIN_Condition
               WHERE Additional_Condition;

 

---

• RIGHT OUTER JOIN

One row of a table and one row of another table are always connected to a common result row – provided that the JOIN condition is fulfilled.In addition, rows of the right table without matching row in the left table are copied to the query result. The missing values (from the left table) are filled with NULL values.

SELECT Column1, Column2, Column3
                FROM Table1 RIGHT OUTER JOIN Table2 ON JOIN_Condition
                WHERE Additional_Condition;

---

• FULL  OUTER JOIN

One row of a table and one row of another table are always connected to a common result row – provided that the JOIN condition is fulfilled.In addition, rows of both tables without matching records are copied to the query result. The missing values (from the other table) are filled with NULL values.

SELECT Column1, Column2, Column3
                FROM Table1 FULL OUTER JOIN Table2 ON JOIN_Condition
                WHERE Additional_Condition;

---