Getting Started With Basic SQL Keywords And SQL Join

This article starts with the basics of the SQL keyword. These keywords are for data filtration in a SQL database table.

From

Command:

Select * From TableName
Select Column From TableName

Example:

select * from Info
select Name from Info

Where

Command:

Select * from TableName where Condition
Select column1, column2 from TableName where Condition

Example:

select * from Info where City = 'Ahmedabad'
select Name from Info where city = 'Surat'

Top

This command selects the top rows in a database table.

Command:

Select Top value from TableName

Example:

select Top 3 * from Info
select Top (25) percent * from Info

As

Use for Alias Keyword.

Command:

Select ExitingColumnName AS NewAliasColumnName from TableName

Example:

Select Name From Info
select Name AS [Person Name] from Info

AND

Command:

select * from TableName where condition1 AND Condition2
select ColumnName from TableName where condition1 AND Condition2

Example:

select * from Info where Name='Rakesh' And City= 'Ahmedabad'
Select * From info where Name='Rakesh' And City = 'Surat'

OR

Command:

select * from TableName where condition1 OR Condition2
select ColumnName from TableName where condition1 OR Condition2

Example:

select * from Info where Name='Rakesh' OR City= 'Ahmedabad'
Select * From info where Name='Rakesh' OR City = 'Surat'

Between

Command:

Select * from TableName where columnCondition Between StartValue AND EndValue

Example:

select * from Info
select * from info where id Between 3 AND 5

IN

Command:

select * from TableName where condition IN (‘DATAValue’)

Example:

select * from Info where city in ('Ahmedabad')
select * from Info where city in ('Goa','Surat')

LIKE

Command:

select * from TableName From ColumnName LIKE ‘%VALUE%’

Example:

select * from Info where city like '%Goa%'
Select * from Info Where City like '%Ahm%'
Select * from Info where Name like '%esh%'
Select * from Info where id like '%3%'

Distinct

Don't display the same multiple records.

Command:

Select DISTINCT ColumnName From TableName

Example:

select City from Info
Select DISTINCT City from Info

Order By

Sorts data in ascending or descending order.

Command:

Select * from TableName Order By ColumnName ASC | DESC
Select columnName from TableName Order By ColumnName ASC | DESC

Example:

Select * from Info order by Name ASC --ascending order by Name Column
Select Name from Info order by Name DESC --descending order by Name Column

Group By

Selects data in a group.

Command:

Select conditionwithcolumn from TableName Group by ColumnName

Example:

select city, count(Name) As Number_of_Person from Info Group by City

Counts the total number of pepole in each city.

SQL Join

A join is the most important concept of SQL databases. Many types of joins are available in SQL Server.

In this, any two or more tables are joined with the same data column field.

Basic Command

Select column1, column2
From Table1
[Type of Join] Table2
ON condition

1. Cross Join
   
   All data records are selected from the selected database table.
   
   Command:
   

   Select column from Table1 CROSS JOIN Table2

   Example:
   

   select EmployeeData.EmployeeID,EmployeeData.EmployeeName,Department.Department
   from EmployeeData
   Cross Join Department

   
   
   If 5 data records are in Table1 and 2 data records are in Table2 then that means in a cross join the output is from both tables and are multiple records.
   Example: [5 record * 2 record = Total 10 records display]
   
   
2. Inner Join
   
   Command:
   

   Select Table1.column,
   Table2.column,
   From Table1
   Inner join Table2 ON Table1.ColumnID = Table2.ColumnID

   Example:
   

   select EmployeeData.EmployeeID,
   EmployeeData.EmployeeName,
   Department.Department
   From EmployeeData
   Inner Join Department
   ON EmployeeData.DepartmentID=Department.DepartmentID

   
   
   
3. Outer Join
   
   Two types of outer joins in SQL database.
   
   Left Outer Join
   
   Command:
   

   Select Table1.column,
   Table2.column,
   From Table1
   Left outer join Table2 ON Table1.ColumnID = Table2.ColumnID

   Example:
   

   select EmployeeData.EmployeeID,EmployeeData.EmployeeName,
   Department.DepartmentID,Department.Department
   From EmployeeData
   LEFT Outer Join Department
   ON EmployeeData.DepartmentID=Department.DepartmentID

   
   
   Right Outer Join
   
   Command:
   

   Select Table1.column,
   Table2.column,
   From Table1
   Right outer join Table2 ON Table1.ColumnID = Table2.ColumnID

   Example:
   

   select EmployeeData.EmployeeID,EmployeeData.EmployeeName,
   Department.DepartmentID,Department.Department
   From EmployeeData
   Right Outer Join Department
   ON EmployeeData.DepartmentID=Department.DepartmentID

Union

Command:

Select column1, column2
from Table1
TypeofJoin Teble2
ON Table1.ColumnID = Table2.ColumnID
UNION
Select column1, column2
from Table1
TypeofJoin Teble2
ON Table1.ColumnID = Table2.ColumnID

Example:

Select EmployeeID,EmployeeName,Department
From EmployeeData
Left outer Join Department
On EmployeeData.EmployeeID = Department.DepartmentID
Union
Select EmployeeID,EmployeeName,Department
From EmployeeData
Right outer Join Department
On EmployeeData.EmployeeID = Department.DepartmentID

Union ALL

Command:

Select column1, column2
from Table1
TypeofJoin Teble2
ON Table1.ColumnID = Table2.ColumnID
UNION ALL
Select column1, column2
from Table1
TypeofJoin Teble2
ON Table1.ColumnID = Table2.ColumnID

Example:

Select EmployeeID,EmployeeName,Department
From EmployeeData
Left outer Join Department
On EmployeeData.EmployeeID = Department.DepartmentID
Union ALL
Select EmployeeID,EmployeeName,Department
From EmployeeData
Right outer Join Department
On EmployeeData.EmployeeID = Department.DepartmentID

I hope you understand about SQL database condition keywords and joins.