Joins and More in SQL

Read other lessons from Kanchana here. 

What is a Join?

Join is used for retrieving data from multiple tables .Using SELECT statements data is retrieved from two or more tables using a join clause. There are different types of joins. We will see two of them in this article.

For the examples discussed in this article we use the tables CUSTOMERS and TRANSACTION .Their table designs are as given.

CUSTOMERS

Transaction 

1.INNER JOIN

SELECT customers.Customer_no, customers. Cust_Name, transaction.Transaction_no FROM customers

INNER JOIN transaction

on customers.Customer_no = transaction.Customer_no ;

The above statement retrieves all records from customers and transaction table where there exists a customer no in customers table that matches the customer no in transaction table.

Given below is the sample records for Customers and Transaction tables where only specific fields of concern are listed.

Transaction Table 

When the Inner Join query is executed we get an output as given below.

The representation in terms of visual diagrams would be as in Figure 1 above 

FULL OUTER JOIN transactionSELECT customers. Customer_no, customers.Cust_Name,transaction.Time_stamp FROM customers

on customers.Customer_no = transaction.Customer_no ;

The Sample Output

The representation in terms of visual diagrams would be like in Figure 2 above 

Where a represents CUSTOMERS table and b represents TRANSACTION table. As seen the records listed are from both the circles completely.