Using Joins SQL Server Examples

SQL SERVER > QUERY > JOIN

Using joins you can retrieve data from two or more tables based on logical relationships between the tables.

create table #category(name varchar(20), description  varchar(20))

insert into #category(name,description)
values('cat 1','cat 1 description')
insert into #category(name,description)
values('cat 2','cat 2 description')
insert into #category(name,description)
values('cat 3','cat 3 description')

create table #product(id int, name varchar(20), category varchar(20))

insert into #product(id,name,category)

values(1,'product 1','cat 1')
insert into #product(id,name,category)
values(2,'product 2','cat 2')
insert into #product(id,name,category)
values(2,'product 3',null)

INNER JOIN  returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column. 

select p.id, p.name, c.description as category

from #product p
inner join #category c
       on p.category = c.name

LEFT OUTER JOIN includes all rows in the a table  whether or not there is a match on the another table.

select p.id, p.name, c.description as category

from #product p
left  join #category c
       on p.category = c.name

RIGHT OUTER JOIN indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

select p.id, p.name, c.description as category

from #product p
right  join #category c
       on p.category = c.name

FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value.

select p.id, p.name, c.description as category

from #product p
full  join #category c
       on p.category = c.name

CROSS JOIN produces the Cartesian product of the tables involved in the join.

 select p.id, p.name, c.description as category
from #product p
cross join #category c

      

drop table #product

drop table #category

ABS SQL Server Example

SQL Server > Built-in Functions > ABS

Returns the absolute positive value of a numeric expression.

Example

DECLARE @i int;
SET @i = -333;
SELECT ABS(@i);

Result:
333


  

Power SQL Server Example

SQL Server > Built-in Functions > POWER

Returns the value of a number to the specified power.

Example:

select power(2,4)

Result:

16  

SPACE SQL Server Example

SQL Server > Built-in Functions > SPACE

Returns a string of repeated spaces.

Example:

declare

@str nvarchar(max),
@str1 nvarchar(max)

set @str='SQL'
set @str1='SERVER'

select @str + SPACE(5) + @str1


Result:
SQL     SERVER  

RIGHT function SQL Server Example

SQL Server > Built-in Functions > RIGHT

Returns the right part of a string with the specified number of characters.

Example:

declare @str nvarchar(max)
set @str='SQL Server'
select right(@str,6)

Result:
Server  

LEFT SQL Server Example

SQL Server > Built-in Functions > LEFT

Returns the left part of a string with the specified number of characters.

Example:

declare @str nvarchar(max)
set @str='SQL Server'
select left(@str,3)

result:
SQL

LEN SQL Server Example

SQL Server > Built-in Functions > LEN

Returns then length of string expression.

Example

declare @str nvarchar(max)

set @str='SQL Server'

select Len(@str)

result:
10

HOST_NAME SQL Server Example

SQL Server > Built-In Functions > HOST_NAME


Represents the workstation name, name of computer connected to SQL Server.

SELECT HOST_NAME()

ROW_NUMBER SQL Server Example

SQL Server > Built-in Functions >  ROW_NUMBER

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example:

CREATE TABLE #LocalTempTable(

       ID            int,
       Name   varchar(50),
       Salary int,
       DeptId int)

insert into #LocalTempTable(Id, Name, Salary, DeptId) values (1,'p1',100,1),  (2,'p2',200,1) ,  (3,'p2', 500,2)

SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS Row,

    Name, Salary
FROM
       #LocalTempTable;

-- specify number of rows

WITH tbl AS
(
    SELECT Id, Name,
    ROW_NUMBER() OVER (ORDER BY Salary) AS RowNumber
    FROM #LocalTempTable
)

SELECT Id, Name, RowNumber 
FROM tbl
WHERE RowNumber BETWEEN 1 AND 2;

drop table #LocalTempTable

Int, bigint, smallint, and tinyint SQL Server

SQL Server > Data Types > Int, bigint, smallint, and tinyint

These type are exact number data types and use integer data.
The bigint data type is used when integer values exceed the range.

bigint	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
int	-2,147,483,648 to 2,147,483,647
smallint	-32,768 to 32,767
tinyint	0 to 255

Example:

Use int type for primary key in a table

CREATE TABLE [dbo].[Table] (        [Id] INT NOT NULL PRIMARY KEY )

Decimal and numeric types SQL Server example

SQL Server > Data Types > decimal and numeric

Numeric is functionally equivalent to decimal.
They have fixed precision and scale.

precision: maximum total number of decimal digits (for both left and right)

scale: number of decimal digits that will be stored to the right of the decimal point.
Example:

declare @num numeric(20,5)
set @num = 1333.2
print @num

declare @num1 numeric(20,0)
set @num1 = 1333.2
print @num1

Result:
1333.20000
1333