SQL Server Inner Join - Concepts and Best practices

In a database such as SQL Server, we create tables which are related to each other through a common column. For example, if we have to store customers’ related information, then the customer’s personal information gets stored in one table and the same customer’s transaction information, gets stored in another table. Both the tables have CustID as a common column.

We can implement RDBMS (Relational Database Management System) concepts through any database, such as SQL Server. So the same entity’s information flows down in different tables. This is part of the Normalization process within RDBMS framework.

However as part of the requirement, we may need to show a report containing customer information from multiple tables, on a matching column condition. Here arises the need of using the technique of Joins in SQL scripting.

Example - Let us create an Employee specific set of 2 tables in a new database.

Execute the following script in your instance of SQL Server.

Use Master
go
Create Database Inner_Join
go
Use Inner_Join
go
CREATE TABLE EMP
       (EMPNO Integer,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer)
go
INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK', 7902, '12/17/80', 800, NULL, 20)
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698, '5/20/81', 1600, 300, 30)
INSERT INTO EMP VALUES
        (7521, 'WARD', 'SALESMAN', 7698, '5/22/81', 1250, 500, 30)
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839, '4/2/81', 2975, NULL, 20)
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698, '9/28/81', 1250, 1400, 30)
INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839, '5/1/81', 2850, NULL, 30)
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839, '6/9/81', 2450, NULL, 10)
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',  7566, '12/9/82', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL, '11/17/81', 5000, NULL, 10)
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698, '9/8/1981',1500,    0, 30)
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK', 7788, '1/12/83', 1100, NULL, 20)
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK', 7698,  '12/3/81', 950, NULL, 30)
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST', 7566, '12/3/81', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',  7782, '1/23/82', 1300, NULL, 10)
go
CREATE TABLE DEPT
       (DEPTNO INTEGER,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) )
go
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK')
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS')
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO')
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON')
go

Let us see the tables to understand the relationship.

use Inner_Join
go
select * from dept
go
select * from emp
go

dept-no

You can observe that Deptno is the common field in both the tables, Dept & Emp. Department details are in Dept table and Employee table contains a DEPTNO column which represents the Department Number an Employee belongs to. Let us see two records of Emp table to get a clarity. Consider the record of SMITH in Emp table. Smith’s Deptno is 20, which means Smith’s Department Name (DNAME) is RESEARCH. Similarly consider ALLEN’s record. His Deptno is 30, which means Allen’s DNAME is Sales. So for the first two records, we have mapped the common column Deptno!
Now we want to display the ENAME and DNAME for the matching Deptno. This is possible by a technique within SQL scripting that is called as Joins.

There are multiple types of joins, but in this article we are going to focus on Inner Join or also known as Equi-Join.

Editorial Note: Check this artilce to understand Types of JOIN in SQL Server - Inner, Self, Outer and Cross JOIN

So what is Inner Join? Inner join is displaying data from multiple tables on matching values of common field (s), within the associated tables.

Inner Join Syntax

There are two types of syntaxes for Inner join:

1) Non-ANSI
2) ANSI

Let us see the Non-ANSI syntax first:

select ename, dname
from emp, dept
where emp.deptno = dept.deptno

non-ansi-sql

One thing to note is that in the from clause of SQL statement, we have two tables separated by a comma. The mapping of common column is done in the where clause.

How does it work internally? Whenever a JOIN command is given, then each record of the first table is evaluated with each record of the second table. That means a Cartesian product happens and then as per the matching values of where clause, those records are shown. As per the Emp and Dept tables, 14 records of Emp are cross evaluated with 4 records of Dept table. So overall 14 * 4 = 56 evaluations will happen and matching value records are shown.

Now, let us remove the where clause and see what happens.

select ename, dname
from emp, dept

where-clause
rows-affected

Oops!! The Cartesian product result is displayed now! Due to the absence of where clause, each evaluation has been displayed as well. So this output is logically going wrong! Actually we have accidentally landed in another type of join that is Cross Join.

Now, let us have the common field for display purpose. We will include the Deptno column along with Ename and Dname columns in the column list.

select ename, dname, deptno
from emp, dept
where emp.deptno = dept.deptno

It has thrown the following error –

ambiguous-column

The common field’s name is same in both the tables. So a confusion arose in understanding that deptno from which table should be considered. The error would not have come if the common column had different name. In such a case where name is same, it is mandatory to mention the table name and . (dot) separator as prefix .

Let us rectify the above query.

select ename, dname, emp.deptno
from emp, dept
where emp.deptno = dept.deptno

inner-join-result

Ideally table name prefix should be mentioned for each column irrespective of whether it is common or uncommon. Advantage of this approach is that it will give clarity to any other user who is not habitual with the tables & its columns.

So the same query will be written as follows:

select emp.ename, dept.dname, emp.deptno
from emp, dept
where emp.deptno = dept.deptno

But in projects, table names many a times are lengthy. So mentioning a lengthy table name repeatedly the will be cumbersome. To avoid that, introduce a table alias in the from clause. Use that alias in the column list and where clause.

JOIN – Best practice

So now I am coming to the best practice of writing a JOIN statement.

select e.ename, d.dname, e.deptno
from emp e, dept d
where e.deptno = d.deptno

Internally, from clause is getting executed first, then the where clause and finally the select clause as per the above statement. 

INNER JOIN – ANSI Syntax

So far we have seen the NON-ANSI syntax of joining tables. Now let us see the ANSI syntax. As per ANSI Joins, there are two changes:

1. The comma within from clause gets replaced by Join type specification
2. The JOIN condition has to be given using ON clause instead of where clause.

The select statement will look like this:

select e.ename, d.dname, e.deptno
from emp e Inner Join dept d
On e.deptno = d.deptno

Amongst the multiple types of joins, the default type of join is an Inner Join. So Inner is an optional keyword. The query can have only Join as the keyword which will be as follows: 

select e.ename, d.dname, e.deptno
from emp e Join dept d
On e.deptno = d.deptno 

In Non-ANSI style of join, we have observed that if the where clause is skipped, then it shows a Cartesian product output. On the same lines, let us try to omit the On clause in ANSI and let’s see what happens.

select e.ename, d.dname, e.deptno
from emp e Join dept d

It is throwing an error.

incorrect-syntax

It means that On clause is mandatory in ANSI type of join. The advantage of ANSI join is that it is a dedicated inner join and it prevents an accidental Cartesian product.

About one-to-many relationship for Inner join

The inner or equi join will be logically correct if we have one-to-many relationship between the values of common field. In the Emp and Dept tables, there is such relationship, so the output is correct, i.e a matching deptno is once in the dept table and it is multiple times in the emp table.

What if there is a many-to-many relationship within the tables? Right now the deptno column is not having a primary constraint in the Dept table. So we can add one more record of the same deptno 10.

Insert into Dept Values(10, 'Testing', 'CALIFORNIA')
Go

So right now there are 2 records of the same deptno 10 in dept table.

Let us see that –

select * from dept

duplicate-records

Now we will execute the same ANSI join statement again.

select e.ename, d.dname, e.deptno
from emp e Join dept d
On e.deptno = d.deptno 

The output has logically gone wrong!

wrong-output

CLARK, KING and MILLER are displayed in both Accounting & Testing departments.

An Inner join will give correct result only when there is one-to-many relationship. Therefore normally this problem gets eliminated when we have Primary Key and Foreign Key relationship within the common field.

Let us delete this additional record of deptno 10 so that everything goes back to normal.

Delete from Dept
where DName = 'Testing'
go

Important features of Inner Join

1. Common field name may be same or different.
2. The data type and size of common fields should be ideally the same.
3. If the data type is not same, then using type casting functions the data types should be made same in the On clause.
4. There should be one-to-many relationship within the common field values.
I hope through this article, your Inner Join principle now is as sound as a dollar!

Shrink a Live SQL Server Database and Logs - Caveats and Best Practices

Updated on August 20th, 2016.

If you have deleted a lot of data in the database and truncated some tables, probably you may want to shrink database files to recover the unused space. Basically shrinking will move the data pages at the end of the file into unoccupied space, available in the front of the file.

You can use the DBCC SHRINKDATABASE command. For example, this command

DBCC SHRINKDATABASE (your_database,10) 

will decrease database size and allow for 10 percent free space. 

You can also shrink log file alone using DBCC SHRINKFILE command Take full back up of the database and run the following :

ALTER DATABASE your_database SET RECOVERY SIMPLE; 

DBCC SHRINKFILE (your_database_Log, 10); 

ALTER DATABASE your_database SET RECOVERY FULL; 

It will set the log file size to 10 MB 

Caveats of shrinking database and logs:

1. SHRINK commands should not be done frequently as it will increase the index fragmentation.

2. It is a fully logged operation which will increase the log size and slows down the performance

3. Shrinking is not possible while the current database is being backed up up or restored

4. Shrinking is not possible when columnstore index is enabled

Best practices

1. Use SHRINK only if a lot of operations such as TRUNCATE and DROP has lead to unused space

2. Take full backup before shrinking database files. If you shrink log file, set the Recovery mode into SIMPLE before shrinking and make it FULL after shrinking

3. Never turn ON AUTO SHRINK option

4. Always rebuild indexes after SHRINKING the database files to avoid fragmentation

SQL Server ISNULL() With Multi Column Names

Many a times we come across null values within tables in SQL Server. Null values are the values with no data; that means the data is missing or unknown. In this article, initially we will understand the SQL Server IsNull function, then we will move towards extending the IsNull functionality using Coalesce function.

Let us see the following example.

create table Identification
(empid Integer,
ename varchar(30) NOT NULL,
passport_number char(15) ,
license_number char(15) ,
pan char(15) ,
credit_card_number char(15) ,
account_number char(15) 
)
insert into identification values(1,'John',null,null,'PN-78654','CC-12345','AN-3456')
insert into identification values(2,'Martin','PS-566774',null,null,null,null)
insert into identification values(3,'Smith',null,null,null,null,null)
insert into identification values(4,'Roger',null,null,null,null,'AN-9876')
insert into identification values(5,'King',null,null,null,'CC-8787','AN-9878')
go
select * from identification
go

sql1-identification-table
Image1-Identification-Table

In the above table, every employee has an identity proof which is either a passport number, license number, pan, credit card or account number.

SQL Server ISNULL() Function Example

Syntax:
IsNull(Parameter1, Value if null)

IsNull function returns the first parameter if it’s not null. If the first parameter is null, then it returns the second parameter.

Suppose we want to see if an employee has a passport or not, here the IsNull function can help us.

See the following example of using SQL Server ISNULL in a Select Statement:

select empid, ename, IsNull(Passport_Number, 'Not Found') 
as 'Passport Status' from identification

sql2-isnull-with-single-column
Image2-IsNull-With-Single-Column

Limitation of IsNull() function:

IsNull function can check only if one value is null. It cannot check null for multiple values. That means it is not capable of handling the functionality of checking if the first parameter is null and then move on to check the next parameter for null.

Now assume that for report generation, we want to get the passport number or license number or pan number etc. for reference purpose. If passport number is null, then we need to extract the license number. If license number is null then pan, if pan is null then account number. If all are null then we need to flag a message ‘Invalid’.

The problem is that IsNull function here needs to be used in a nesting manner.

Let us see the ISNULL being used in a select statement in a nested fashion:

select empid, ename, 
IsNull(Passport_Number,IsNull(License_Number, 
IsNull(PAN, IsNull(Credit_Card_Number, IsNull(Account_Number,'Invalid'))))) 
as "Status"
from identification

sql3-isnull-multiple-columns
Image3-IsNull-Multiple-Columns

In the above select statement, we have used IsNull function 5 times to get the desired output.

ISNULL vs Coalesce Function:

There is an alternative way using another SQL Server function known as Coalesce.

Syntax of Coalesce:
COALESCE( parameter1, parameter2, parameter3,……. parameter_n , default_parameter)

Coalesce can take multiple parameters. It returns the first not null parameter. If all the parameters are null, then it will return the default parameter.

In other words, we can say that coalesce behaves as per the following syntax as well:

CASE
   WHEN (parameter1 IS NOT NULL) THEN expression1
   WHEN (parameter2  IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

So instead of nesting IsNull function multiple times, we can use a single coalesce function and get the same output as shown here:

select empid, ename, 
Coalesce(Passport_Number,License_Number,PAN,Credit_Card_Number,Account_Number,'Invalid')
as "Using Coalesce" from identification

sql4-coalesce
Image4-Coalesce


Conclusion:

We have seen how the SQL Server IsNull function is suitable for checking one null value and how usage of coalesce function can eliminate the need of using IsNull function redundantly.

SQL Server T-SQL DateDiff Example

How do we find the difference between two dates in SQL Server. Not just the difference in the number of days, but also number of weeks, months.

The answer is by using DateDiff in SQL Server. Datediff is also suitable for getting the time elapsed between the start and end of a process.

Here are some real life examples of how t-sql datediff can be used to get a date difference:

1. Calculating the number of days remaining for a postpaid mobile service before it expires? Here an Automated system can calculate the date difference in sql and send an SMS to a customer informing him/her of the number of days remaining.

2. Particularly suitable in embedded systems to note the time taken for each process while manufacturing a product.

3. In library management automation to keep track of the number of days a book had been issued to a customer.

4. For daily login-logout required in an electronic attendance software system of any company. The tsql datediff function can be used to calculate the exact working hours of every employee.

The signature of this function is as follows:

DATEDIFF( interval, date1, date2 )

where date 1 & date 2 can be the actual date or a part of date like an year.

Note: date1 should be greater than or equal to date2.

Interval here can be any of the following as shown in the following chart:

t-sql-date-diff
image1-t-sql datediff interval

Let us see some examples to make it more clear and interesting.

select datediff (yy, '1984', '1997')  -- 13 years difference

select datediff (dd, '1984', '1986') -- 731 days difference

select datediff (mm, '1984', '1986') -- 24 months difference

select datediff (qq, '1984', '1986') -- 8 quarters difference

select datediff (hour,'2016/05/02 11:00', '2016/05/02 14:45' 
-- 3 hours difference

Database Oriented Example


The DateDiff SQL function can also be used in a where clause.

Let us create a sample database for the same in our SQL Server instance.

Use Master
go
create database DateDiff_Demo
go
Use DateDiff_Demo
go
create table Compaints_Details
(ComplaintID Integer Identity,
 CustId Varchar(7),
 Complaint_Description Varchar(100),
 Engineer_ID Varchar(4),
 Date_of_Complaint date,
 Date_of_Resolve date
 )
 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
 Date_of_Complaint,Date_of_Resolve)
 Values
 ('C1', 'Modem problem', 'E1', '21-Apr-2016', '24-Apr-2016')

 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C2', 'Wire Connection problem', 'E1', '22-Apr-2016', '22-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C3', 'Socket problem', 'E1', '23-Apr-2016', '28-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C5', 'LAN problem', 'E1', '29-Apr-2016', '29-Apr-2016')
 GO
 select* from Compaints_Details
 go

sql datediff sample data
Image2-SampleData to run a sql datediff query

Let us say we want to see how many days were elapsed for every complaint to get resolved.

Here we will use the Tsql DateDiff function.

SELECT  [ComplaintID]
      ,[CustId]
      ,[Complaint_Description]
      ,[Engineer_ID]
      ,[Date_of_Complaint]
      ,[Date_of_Resolve]
      , DateDiff(dd, [Date_of_Complaint]
      , [Date_of_Resolve]) as [Days taken to resolve]
FROM [DateDiff_Demo].[dbo].[Compaints_Details]

tsql datediff days
Image3-DaysTaken before a complaint was resolved


Conclusion:


And that’s how we can use the SQL Server T-SQL DateDiff function to calculate the day, month, year, and time part differences between two specified dates.

SQL Server: Return Newly Inserted Row

Whenever you insert a new row in a SQL Server table that contains default values, you may want to return the newly inserted row to the user, along with the default value generated. However while doing a plain INSERT statement, all you get is the total number of rows affected.

Here’s an example:

insert-row

Here we are using the NEWID() to generate a unique customer number in each row. However as you can see, the user does not get to see the default ID that got generated for the newly inserted rows. In order to get back the row values that were inserted, use the OUTPUT clause of the INSERT statement as shown here:

INSERT INTO #TT (Name, AreaCode)
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.AreaCode
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388
and this time you get to see the newly inserted rows:

sql-server-output-insert

As you can see, we have added the OUTPUT clause right after the INSERT statement. The rows inserted into the table are captured in the virtual table INSERTED and returned back as a result set.

In case you are wondering, yes it is possible to capture the result set in a table or table variable. Assuming there is a Table variable called @TempTbl, just use the following:

INSERT INTO #TT (Name, AreaCode)
OUTPUT INSERTED.ID, INSERTED.Name, INSERTED.AreaCode
INTO @TempTbl
SELECT 'Suprotim', 2355 UNION ALL
SELECT 'Anush', 2388
and now you can do further processing on this data using @TempTbl.

SQL Server 2012 Query Pagination

This article was authored by Praveen Dabade.

In SQL Server 2012, Microsoft introduced a couple of T-SQL Enhancements. One of them is Query Pagination which we will explore today.

For this demonstration, I am using the Northwind database to demonstrate this new feature. Now in most of the applications, a common requirement is how to fetch the data from the database servers, page wise.

In earlier versions of SQL Server like SQL Server 2005/2008/R2, we can implement Pagination by using different techniques. For example, we implement pagination using ROW_NUMBER() function or CTE - Common Table Expression.

In SQL Server 2012, Microsoft has introduced Pagination as a part of Select query in a Order By clause. Now you will have to use OFFSET and FETCH NEXT with the order by clause.

Let's take a look at a few examples. I am using Northwind database for this demonstration. I have created a Stored Procedure which takes two parameters. First parameter takes the page number and the second parameter ask you to fetch the no. of records for that page. The stored procedure code is as below -

query-pagination

If you execute the above stored procedure

EXEC FetchPagedRecords 2,10

you will get the following results -

image

The OFFSET specifies the number of rows to skip before it starts returning the rows and FETCH NEXT specifies the number of rows to be returned.

Microsoft has introduced an easy way of implementing Data Paging in SQL Server 2012 by adding OFFSET and FETCH NEXT in an Order By clause. I hope you will use it in your applications.

Calculate SQL Azure Database Size

I was looking out for a correct way to programmatically determine the size of a SQL Azure database. After searching many solutions, I finally found one shared by Dimitri Furman of the SQL Server team.

His solution involves using the sys.database_files dmv and the FILEPROPERTY function with the ‘SpaceUsed’ argument.

To those new to sys.database_files, this system catalog view stores information and properties about each file for a database. Since it is a db-level view, it gives information about files in the current database only. Five properties that could be of interest are: logical filename, physical filename, initial size, maximum size and a growth increment.

To determine how much space is used in a file, you can use FILEPROPERTY with SpaceUsed.

Here’s an example: SELECT FILEPROPERTY(‘SomeFile’, ‘SpaceUsed’); I have often used the FILEPROPERTY function in the past while monitoring the progress of a SHRINK operation.

Query for calculating Size of a SQL Azure Database

Here’s how to combine sys.database_files with FILEPROPERTY to programmatically calculate the size of a SQL Azure database.

SELECT 
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) 
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024 
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Let’s understand this query.

FILEPROPERTY() returns an int value for a file name stored within sys.database_files. Since sys.database_files is a db-level view, it gives information about files in the current database only. If a file is not present, null value is returned.

Since SpaceUsed represents "pages" and a page is 8 KB in SQL Server, so multiplying by 8192 gets the total bytes. Then dividing two times by 1024 converts the output to MB, and dividing by three times by 1024 converts the output to GB.

CAST is for casting the value to type bigint

If anybody is wondering about the dot (.) after an 8192, then it is to convert the result implicitly to a decimal value.

Please note that logs are excluded for the purposes of determining database size.

To know about Azure SQL Database resource limits, check https://docs.microsoft.com/en-in/azure/sql-database/sql-database-resource-limits

Remove Non-Alphabet Characters from a String–SQL Server

Suppose you have a string that contains numbers and other special characters and you want to keep only alphabets and remove all the other characters. One way to do this is to use a while loop that parses each character

Here is the code for the same

declare @str varchar(20)
set @str='ab12#89L(h12k'
Select @str as 'Original String'
declare @temp_str varchar(20), @i int
select @temp_str ='',@i=1
while @i<=len(@str)
begin
    set @temp_str=@temp_str+case when substring(@str,@i,1) like '[a-zA-Z]' then substring(@str,@i,1) else '' end
    set @i=@i+1
end
select @temp_str as 'String with Alphabets'


The code inside the While loop takes each character and check if it is in the range a-z or A-Z and appends that character with another variable. Other characters will not get appended. So at the end of while loop, the second variable will have only alphabets.

image

GroupBy Clause - SQL Server vs MySQL

A GROUP BY Clause is used to group the data based on specific columns along with summary information. However there are some differences in usage of this clause in SQL Server and MySQL

Let us create this testing table with some sample data

create table testing
(
    sales_id int,
    product_id char(7),
    sales_date datetime,
    sales_amount decimal(12,2)
)


insert into testing(sales_id,product_id,sales_date,sales_amount)
select 1,'PR00001','2001-01-01',1200.00 union all
select 2,'PR00002','2003-01-21',3000.50 union all
select 3,'PR00002','2003-01-21',2500.00 union all
select 4,'PR00001','2002-02-15',1000.00 union all
select 5,'PR00003','2005-12-19',3000.50 union all
select 6,'PR00003','2006-11-01',8000.00 union all
select 7,'PR00004','2007-04-22',350.00 union all
select 8,'PR00004','2007-04-22',590.00 union all
select 9,'PR00004','2007-04-22',590.00 union all
select 10,'PR00001','2008-05-27',4800.50


If you want to get total sales amount for each product, you can write this query both in SQL Server and MySQL

select product_id,sum(sales_amount) as sales_amount from testing
group by product_id


As per ANSI SQL, all columns that are not part of aggregate functions should be included in GROUP BY clause

If you run the following code in SQL Server

select product_id,sum(sales_amount) as sales_amount from testing

You will get an error

Msg 8120, Level 16, State 1, Line 1
Column 'testing.product_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


However if you run this code in MySQL, you will get the following result

Product_id        sales_amount
-----------        --------------
PR00001        25031.50

Because MySQL does the auto grouping for the columns specified in the SELECT statement, if they are omitted in GROUP BY clause, it just simply displays the first value of columns along with total of summary column. In this case, it displays the first product id and total of all products

The following is also possible in MySQL

select *,sum(sales_amount) as sales_amount from testing

As explained, it will list out all columns of first row along with total of sales_amount. You need to aware of this feature in MySQL while using GROUP BY Clause

Nested Case Statement in SQL Server

This article is a continuation of SQL Server CASE Statement and CASE WHEN Examples . Today we will learn about Nested Case Statement in SQL Server.

We can nest CASE statements similar to nested ifs that we find in most programming languages.

Let us see an example.

select ename, job, sal, case  -- Outer Case
    when ename like 'A%' then
    case when sal >= 1500 then 'A' -- Nested Case
    end
    when ename like 'J%' then
    case when sal >= 2900 then 'J' -- Nested Case
    end
end as "Name-Grade"            
From Emp

7-nested-case
Image 7-Nested-Case

Limit of nesting a CASE function is up to 10 levels only.

In the following example, the limit of 10 is completely utilized.

Declare @x int
set @x = 1
Select 
case when @x <= 100 then  -- Level 1
 case when @x <= 90 then -- Level 2
    case when @x <= 80 then -- Level 3
        case when @x <= 70 then -- Level 4
            case when @x <= 60 then -- Level 5
                case when @x <= 50 then -- Level 6
                    case when @x <= 40 then -- Level 7
                       case when @x <= 30 then --Level 8
                         case when @x <= 20 then--Level 9
                           case when @x<= 10 then--Level 10
                                100
End End End End End End End End End 
End as "Nested Case" 
--Ending all levels!

8-nested-case-upto-10-levels
Image 8-Nested-Case-Upto-10-Levels

If nesting is exceeding 10 levels, then SQL Server throws an error.
Declare @x int
set @x = 1
Select 
case when @x <= 100 then  -- Level 1
case when @x <= 90 then -- Level 2
case when @x <= 80 then -- Level 3
    case when @x <= 70 then -- Level 4
        case when @x <= 60 then -- Level 5
            case when @x <= 50 then -- Level 6
                case when @x <= 40 then -- Level 7
                   case when @x <= 30 then --Level 8
                     case when @x <= 20 then--Level 9
                       case when @x<= 10 then--Level 10
                          case when @x <= 11 then -- 11
                             100
End End End End End End End End End End 
End as "Nested Case"

Msg 125, Level 15, State 4, Line 14
Case expressions may only be nested to level 10.