SQL Tutorial 9: SQL Joins


SQL Tutorial 9: SQL Joins

The SQL Joins clause is 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.

Consider the following two tables -

Table 1 - CUSTOMERS with ID, NAME, AGE, and CITY Fields...
Table 2 - ORDERS Table with ID, Year, and Amount Fields...

Combine these two tables and create a Result table with ID, NAME, and Amount Fields

Steps: 
//Create a Database
Create Database gcreddyDB;
------------------------
Use Database;

//Create a Table in the Database,
Create Table Customers
(
ID int,
Name char(40),
Age int;
City char(30);
);
------------------------
//Insert Data...
Insert Into Customers(Id, Name, Age, City) 
values(1, 'Ramesh', 32, 'Hyderabad'),
(2, 'Prasad', 25, 'Delhi'),
(3, 'Venkat', 23, 'Mumbai'),
(4, 'Ramya', 25, 'Chennai');
------------------------
//Create another table
Create Table Orders
(
OrderNo int,
Item char(30),
Amount int;
);
------------------------
//Insert Data...
Insert Into Customers(OrderNo, Item, Amount) 
values(2, 'TV', 5000),
(3, 'Fridge', 4000),
(4, 'AC', 23, 6000);
------------------------
Table 1 - Customers Table
ID
Name
Age
City
1
Ramesh
32
Hyderabad
2
Prasad
25
Delhi
3
Venkat
23
Chennai
4
Ramya
25
Mumbai

Table 2 - ORDERS Table
OrderNo
Item
Amount
2
TV
5000
3
Fridge
6000
4
AC
8000
SQL Tutorial For Beginners
Display Tables one by one,

Select * from Customers;
Select * from Orders;

Now, let us join these two tables in our SELECT statement as shown below.

SQL> SELECT ID, NAME, AGE, AMOUNT
   FROM CUSTOMERS, ORDERS
   WHERE  CUSTOMERS.ID = ORDERS.OrderNo;
This would produce the following result.
ID
Name
Item
Amount
2
Prasad
TV
5000
3
Venkat
Fridge
6000
4
Ramya
AC
8000

Note: Here, it is noticeable that the join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN,  LIKE, and NOT; they can all be used to join tables. However, the most common    operator is the equal to symbol.
------------------------------------------------------------------------
SQL Step by Step Tutorial

1) Introduction to SQL
(http://www.gcreddy.com/2016/07/introduction-to-sql.html)

2) SQL Overview
(http://www.gcreddy.com/2016/08/sql-overview.html)

3) MS SQL Server Installation
(http://www.gcreddy.com/2016/09/sql-environment-setup.html)

4) Database Fundamentals and SQL Language Elements
(http://www.gcreddy.com/2016/11/database-fundamentals-and-sql-language.html)

5) SQL Data Definition Language
(http://www.gcreddy.com/2016/11/sql-data-definition-language.html)

6) SQL Data Manipulation Language
(http://www.gcreddy.com/2017/06/sql-tutorial-6-data-manipulation.html)

7) SQL Operators
(http://www.gcreddy.com/2017/06/sql-operators.html)

8) the SQL Select Query...
(https://youtu.be/kid__0MNRps)

0 comments:

Post a Comment