Monday, March 20, 2017

What is difference between SQL, T-SQL and PL/SQL?

Today, we are going to see another common and interesting SQL interview question, what is the difference between SQL, T-SQL and PL/SQL? It is also one of the most common doubt among SQL beginners. It's common for programmers to think that why there are many types of SQL languages, why not just single SQL across DB? etc. Well, let's first understand the difference between SQL, T-SQL and PL/SQL and then we will understand the need of these dialects. SQL is standard for querying, inserting and modifying data in relational database. It is categorized into DDL and DML and powerful enough to create a database objects e.g. table, view, stored procedure and can perform CRUD operation (SELECT, INSERT, UPDATE, and DELETE) query.

On the other hand, T-SQL (Transact-SQL) is a dialect used by Microsoft SQL Server and Sybase. It is an extension of SQL and provides more functionality than SQL but at them same time confirming ANSI SQL standard as well. For example, you can use conditionals and loops in T-SQL to create a more sophisticated stored procedure which is not available in standard SQL.

Similarly, PL/SQL (Procedural language SEQUEL) is dialect for Oracle database, which provides T-SQL like functionality e.g. conditionals, loops and other elements for procedural programming. Both T-SQL and PL/SQL are the superset of SQL because they not just confirm ANSI SQL standard but also provide additional functionality which is not available in the ANSI standard but help a lot in database programming. In this article, we will see a couple of more differences between SQL, T-SQL and PL/SQL to understand them better.

Why you need T-SQL or PL/SQL?

Though standard SQL is enough for inserting, retrieving and modifying data from the database, they only provide set based operations, which means there are a lot of tasks which you cannot do using the plain SQL. In order make SQL more powerful and to expand its usage from simple querying to create complex stored procedures for report generation, XSLT transformation, and many other functionalities, various database vendor started adding proprietary features on SQL supported by their platform. These efforts created different SQL dialects e.g. T-SQL, which is a SQL dialect for Microsoft SQL Server and Sybase, PL/SQL which is a SQL dialect for Oracle.

In fact, every database has their own SQL dialect, which comprises features and keywords only supported in their database e.g. MySQL has the LIMIT keyword which can be used for pagination or solving problems like second highest salary, but it will not work on Oracle or Microsoft SQL Server database. Similarly, PostgreSQL has some features which are not available to other databases.

It's always recommended to use standard ANSI SQL if it serves your purpose because query written in ANSI SQL is portable across different database vendors but if you use a proprietary keyword e.g. TOP in Microsoft SQL Server, LIMIT in MySQL then you need to change your query when your application migrate from one database to another.

Some more difference between SQL, T-SQL and PL/SQL

1) SQL stands for Structure Query language, T-SQL stands for Transact-SQL and PL/SQL stands for Procedural language/SQL.

2) SQL is supported across all database vendors e.g. Oracle, SQL Server, MySQL, PostgreSQL, IBM DB2 and even lightweight database e.g. SQLLite, but T-SQL is only supported in Microsoft SQL Server and Sybase, and PL/SQL is supported only in Oracle.

3) Another key difference between SQL and PL/SQL, T-SQL is the performance improvement by saving database roundtrip. Both PL/SQL and T-SQL allows grouping of SQL statements which means if your code has 4 SELECT SQL queries then instead of making four round trips to the database, they can be sent as one single unit to the database and their result will also come back as one unit. I suggest reading Oracle PL/SQL Programming 6th Edition by Steven Feuerstein and Bill Pribyl to learn more about PL/SQL programming. It covers versions through Oracle Database 12c.

What is difference between SQL, T-SQL and PL/SQL?

4) There is an interesting difference between SQL and T-SQL in terms of minimum SELECT query requirements. According to standard SQL, a SELECT query must have at minimum FROM and SELECT clauses, but you can create a SELECT query in T-SQL with just a SELECT clause, without FROM clause. For example, following SQL query is invalid according to SQL standard but it works fine in T-SQL supported database e.g. Sybase and MSSQL:

SELECT 'Java' AS Language, 1 AS RANK;

Output of query is single row with attributes resulting from the expression with names assigned using the aliases e.g.

Language Rank
Java     1

If you want to learn more about T-SQL, I suggest reading Microsoft SQL Server 2012 T-SQL Fundamentals, a great reference to T-SQL but at the same time very readable and clear explanation of key SQL concepts.

5) There are some data types which are supported only by PL/SQL and T-SQL e.g. TINYINT data type is only available in T-SQL and VARCHAR2 and NUMBER is only available in PL/SQL or Oracle database. Similarly, there are keywords which are only available in a particular SQL dialect e.g LIMIT keyword which is only available in MySQL.

difference between SQL vs T-SQL vs PL/SQL?

If you want to learn more about features, keyword, and data types supported by different vendors I strongly suggest reading SQL in Nutshell, it provides a great cross-platform syntax for SQL. Absolutely must read for those programmers who work with multiple databases.

That's all on the difference between SQL, T-SQL and PL/SQL. Just remember that both T-SQL and PL/SQL are dialects of SQL, which is standard specified by ANSI for managing data in relational databases. T-SQL is only supported in Sybase and SQL SERVER, while PL/SQL is only supported in Oracle database. Though both T-SQL and PL/SQL is more powerful than SQL and provides several languages construct to do more with database e.g. conditionals, loops, branching etc.

Further Learning
Introduction to SQL by Jon Flanders
Introduction to SQL Server
Head First SQL

Other Database and SQL Interview Questions you may like
  • Difference between Primary key and Foreign key in SQL? (answer)
  • Difference between row_number, rank, and dense_rank in SQL? (answer)
  • Difference between Primary key and Unique key in SQL? (answer)
  • When to use truncate vs delete command in SQL? (answer)
  • Difference between LEFT and RIGHT Outer Join in SQL? (answer)
  • Difference between Clustered and Non-Clustered index in SQL? (answer)
  • Top 6 SQL Query Interview Questions for Programmers (list)
  • Difference between WHERE and HAVING clause in SQL? (answer)
  • Difference between UNION and UNION ALL in SQL? (answer)
  • Difference between View and Materialized view in SQL? (answer)
  • Difference between Primary key and Candidate key in SQL? (answer)
  • Difference between Correlated and Regular Subquery in SQL? (answer)
  • Difference between IsNull() and Coalesce() in T-SQL? (answer)
  • Difference between GETDATE, SYSDATETIME, and GETUTCDATE in T-SQL? (answer)
  • Difference between Self Join and Equi Join in SQL? (answer)
  • Difference between close and deallocate cursor in SQL? (answer)
  • Difference between CHAR and VARCHAR data type in SQL? (answer)
  • 5 SQL Books Every Programmer Should Read (books)

Thank you for reading this article, if you like this tutorial then please share with your friends and colleagues. If you have any feedback or suggestion then please drop a comment. 

No comments :

Post a Comment