Home > Documentation > SQL Server to MySQL

When migrating databases from SQL Server to MySQL server it is often necessary to translate MS SQL queries according to MySQL syntax as well. Syntax of SQL queries in SQL Server and MySQL are similar but not identical. This article discovers 10 most popular differences between MS SQL and MySQL syntax. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

1. Sometime SQL Server table or column names are enclosed in square brackets in queries (e.g. if contains spaces or for some other reasons). MySQL does not allow square brackets around table of column names, they all must be replaced by ` symbol or cut off: [object] -> `object`.

   Also, SQL Server provides effective solution to avoid naming objects conflict and to manage user permissions on data access. This is schema, a logic container used to group and categorize objects inside the single database. When using schema the full name of database object in query may look like database.schema.object. However, there is no such semantic in MySQL, so all schema names must be cut off from queries.

2. SQL Server operator '+' allows to concatenate strings like this: 'string1' + 'string2'. In MySQL such expressions must be replaced by CONCAT('string1', 'string2').
3. If SQL Server query contains 'TOP (100) PERCENT' pattern just cut it off when composing MySQL query. If there is another percentage amount in that pattern, it can be replace by the following code in MySQL (works in MySQL 5.0.7 and higher):

   SET @amount =(SELECT COUNT(*) FROM %table name%) * %percentage% / 10; 
   PREPARE STMT FROM '%original query% FROM %table name% LIMIT ?'; 
   EXECUTE STMT USING @amount; 
   

4. Syntax of JOIN constructions are very similar in SQL Server and MySQL. The only difference is that MS SQL keyword WHERE is replaced by ON in MySQL. For example:

   ... table1 CROSS JOIN table2 WHERE condition
   

   must be translated into

   ... table1 CROSS JOIN table2 ON condition
   

5. SQL Server offers OUTER APPLY to join two table expressions with evaluating right-side expression for each row from the left-side one:

   SELECT c.ID, c.FirstName, c.LastName, c.PhoneNumber, p.Id, p.Debit, p.Credit
   FROM customers c OUTER APPLY
   (
   	SELECT p.* FROM payments p
   	WHERE c.CustNumber = p.CustNumber
   	ORDER BY p.id DESC
   ) p
   

   It must be replaced by LATERAL JOIN in MySQL:

   SELECT c.ID, c.FirstName, c.LastName, c.PhoneNumber, p.Id, p.Debit, p.Credit
   FROM customers c, LATERAL JOIN
   (
   	SELECT p.*
   	FROM payments p
   	WHERE c.CustNumber = p.CustNumber
   	ORDER BY p.id DESC
   ) p
   

6. There is a common way to join elements into comma separated string via FOR XML PATH syntax pattern in SQL Server:

   SELECT distinct r.price, STUFF(
   	(SELECT distinct ','+ Cast(a.code as varchar) FROM tbl_price a 
   	WHERE r.price = a.price FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') 
   FROM tbl_price r
   

   Those queries must be rewritten using GROUP_CONCAT in MySQL:

   SELECT price, GROUP_CONCAT(DISTINCT code) code
   FROM tbl_price
   GROUP BY price
   

7. convert SQL Server built-in functions into MySQL equivalents according to this article

Have questions? Contact us