INNER JOIN and LEFT JOIN - SQL Query
The Joins are used to combine rows from two or more tables, based on the common field between two tables.
The telecom company has a list of some clients and transactions of their paid bill payments.
Consider the following tables Table1 and Table2.
Table1: Clients
| C_ID | Client_Name | Client_Phone |
|---|
| 1 | Alex | 9022222222 |
| 2 | Mark | 9033333333 |
Table2: Payment
| C_ID | Transaction_ID | Amount |
|---|
| 1 | 101 | 355 |
| 2 | 102 | 255 |
| 3 | 103 | 300 |
| 4 | 104 | 400 |
INNER JOIN
Q. Write a query to display the record that contain columns such as C_ID, Client_Name, Amount from Table1 and Table2.
Answer:
The inner join selects all rows from both columns, if there is a match between them. The column 'C_ID' is common to both column.
SELECT Client.Client_Name, Payment.Transaction_ID, Payment.Amount
FROM Clients
INNER JOIN Payment
ON Payment.C_ID = Clients.C_ID
| C_ID | Client_Name | Amount |
|---|
| 1 | Alex | 355 |
| 2 | Mark | 255 |
LEFT JOIN or LEFT OUTER JOIN
Q. From given tables, write a query to display record that contain columns such as C_ID, Client_Name, Amount from Table1 and Table2.
Answer:
Consider table1 as left table and table2 as right table.
SELECT Clients.Client_Name, Clients.Client_Phone, Payment.Amount
FROM Clients
LEFT JOIN Payment
ON Payment.C_ID = Payment.C_ID
| C_ID | Client_Name | Amount |
|---|
| 1 | Alex | 355 |
| 2 | Mark | 255 |
