Differences in SQL Syntax between MS SQL Server and MySQL

Here are the differences in SQL syntax between MSSQL and MySQL

In MSSQL the table names and column names are enclosed in double quotes or square brackets whereas in MySQL table names and column names are enclosed in backtick (“`”)  character


Example 

MSSQL

CREATE TABLE "Employees" ("Empno"  VARCHAR(10),"EmpName" Varchar(100) ......

SELECT [Empno],[EmpName] from "Employees" ......

MySQL

CREATE TABLE `Employees` (`Empno` VARCHAR(10),`EmpName` Varchar(100) ......

SELECT `Empno`,`EmpName` from `Employees` ......

Enclosing identifier names in enclosing character is optional in both database softwares, but if the identifier name contains blank spaces then it becomes mandatory to enclose it within double quotes or square brackets.

For Example the following statement can be written like this

MSSQL

CREATE TABLE "Employees" ("Empno"  VARCHAR(10),"EmpName" Varchar(100) ......

MySQL

CREATE TABLE `Employees` (`Empno` VARCHAR(10),`EmpName` Varchar(100) ......

or without enclosing character like this.

MSSQL

CREATE TABLE Employees (Empno  VARCHAR(10),EmpName Varchar(100) ......

MySQL

CREATE TABLE Employees (Empno  VARCHAR(10),EmpName Varchar(100) ......

but if the identitfier name contains blank spaces then you have to enclosed it with double quotes or square brackets

MSSQL

CREATE TABLE "Employees Table" ("Emp No"  VARCHAR(10),"EmpName" Varchar(100) ......

MySQL

CREATE TABLE `Employees Table` (`Emp No` VARCHAR(10),`EmpName` Varchar(100) ......

you can't  write it like this

MSSQL

CREATE TABLE Employees Table (Emp No  VARCHAR(10),EmpName Varchar(100) ......

MySQL

CREATE TABLE Employees Table (Emp No VARCHAR(10),EmpName Varchar(100) ......

In MySQL if you turn on ANSI_QUOTES  SQL mode option with the following command

mysql> SET sql_mode='ANSI_QUOTES';

then MySQL also allows to quote identifiers within double quotation marks. But remember when you enable this option you cannot quote literal strings in double quotes in SQL statements. you have to use only single quotes for quoting  literal strings in SQL statements

Case Sensitive

In MS SQL if a database is created with Case Sensitive COLLATION then table names and column names are case sensitive otherwise, if the database is created with a Case Insensitive Collation then identifier names are case insensitive

For Example

If you created a table in Case Sensitive Collation database, like this

create table Employee (SNo int,Name Varchar(100),Sal money)

Notice the captial E in tablename

 Then if you give the following command

select * from employee

It will give error

Invalid object name 'employee'.

You have to mention the tablename in same case as you have specified at the time of creation.

select * from Employee

MySQL

In MySQL there is no case sensitiveness in Schema Names

TOP 'n' Rows

MSSQL

In MS SQL Server to view top 'n' rows we have to give TOP keyword after the SELECT clause. For Example to view top 5 salaries of employees we have to give a query like this

SELECT TOP 5 [Empno]
,[Name]
,[Salary]
,[Jdate]
FROM [Scott].[dbo].[Emp] order by salary desc

 

 

MySQL

In MySQL the equivalent o TOP n rows can be achieved by using LIMIT n keyword.
For Example the equivalent query for the above MSSQL query would be

select * from emp order by sal desc limit 5