Difference between SQL, T-SQL and PL/SQL?

Hello guys, if you are preparing for SQL and Database Interviews or any Software engineering interview and looking for difference between T-SQL, SQL, and PL/SQL then you have come to the right place. Earlier, I have shared 50 SQL Interview questions and 12 SQL query Examples from interviews and 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 doubts 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 for these dialects. 

SQL is standard for querying, inserting, and modifying data in a relational database. It is categorized into DDL and DML and is powerful enough to create 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 the 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 that is not available in standard SQL.

Similarly, PL/SQL (Procedural language SEQUEL) is a 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 supersets of SQL because they not just confirm ANSI SQL standard but also provide additional functionality that is not available in the ANSI standard but helps a lot in database programming.

In this article, you will learn  a couple of more differences between SQL, T-SQL, and PL/SQL to understand them better.

Why do 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 that you cannot do using plain SQL.

In order to 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 vendors 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 its 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.

Differences between SQL, T-SQL and PL/SQL

Here are a couple of more differences between SQL, PL/SQL, and T-SQL for interviews:

1. Full form

SQL stands for Structured Query language, T-SQL stands for Transact-SQL and PL/SQL stands for Procedural Language/SQL.

2. Supported Database

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


3. Performance

Another key difference between SQL and PL/SQL, T-SQL is the performance improvement by saving database roundtrip. Both PL/SQL and T-SQL allow 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 joining these best Oracle and PL/SQL online courses to learn more about PL/SQL programming. It covers versions through Oracle Database 12c.

4. SQL Query Requirement

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, the following SQL query is invalid according to SQL standard but it works fine in T-SQL supported databases like Sybase and MSSQL:

SELECT 'Java' AS Language, 1 AS RANK;

The output of the query is a 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 joining these Microsoft SQL Server and T-SQL Fundamentals courses, a great reference to T-SQL but at the same time a very readable and clear explanation of key SQL concepts.

5. Data Types and Keyword

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 like the LIMIT keyword which is only available in MySQL.

If you want to learn more about features, keyword, and data types supported by different vendors I strongly suggest joining these best SQL courses, 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 the 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 the Oracle database. Though both T-SQL and PL/SQL are more powerful than SQL and provide several languages construct to do more with database e.g. conditionals, loops, branching, etc.


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. 

P.S. - If you are new SQL and Database world and looking for some free resources to start your journey then you can also take a look at this list of Free SQL Courses for Beginners to start your SQL and Database journey for FREE.