[sql] What is the difference between SQL, PL-SQL and T-SQL?

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

Can anyone explain what the differences between these three are, and provide scenarios where each would be relevantly used?

• SQL a language for talking to the database. It lets you select data, mutate and create database objects (like tables, views, etc.), change database settings.
• PL-SQL a procedural programming language (with embedded SQL)
• T-SQL (procedural) extensions for SQL used by SQL Server

SQL is a standard and there are many database vendors like Microsoft,Oracle who implements this standard using their own proprietary language.

Microsoft uses T-SQL to implement SQL standard to interact with data whereas oracle uses PL/SQL.

Structured Query Language - SQL: is an ANSI-standard used by almost all SGBD's vendors around the world. Basically, SQL is a language used to define and manipulate data [DDL and DML].

PL/SQL is a language created by Oracle universe. PL/SQL combine programming procedural instructions and allows the creation of programs that operates directly on database scenario.

T-SQL is Microsoft product align SQL patterns, with some peculiarities. So, feel free to test your limits.

SQL

SQL is used to communicate with a database, it is the standard language for relational database management systems.

In detail Structured Query Language is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

Originally based upon relational algebra and tuple relational calculus, SQL consists of a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

PL/SQL

PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation

Specialities of PL/SQL

• completely portable, high-performance transaction-processing language.
• provides a built-in interpreted and OS independent programming environment.
• directly be called from the command-line SQL*Plus interface.
• Direct call can also be made from external programming language calls to database.
• general syntax is based on that of ADA and Pascal programming language.
• Apart from Oracle, it is available in TimesTen in-memory database and IBM DB2.

T-SQL

Short for Transaction-SQL, an extended form of SQL that adds declared variables, transaction control, error and exceptionhandling and row processing to SQL

The Structured Query Language or SQL is a programming language that focuses on managing relational databases. SQL has its own limitations which spurred the software giant Microsoft to build on top of SQL with their own extensions to enhance the functionality of SQL. Microsoft added code to SQL and called it Transact-SQL or T-SQL. Keep in mind that T-SQL is proprietary and is under the control of Microsoft while SQL, although developed by IBM, is already an open format.

T-SQL adds a number of features that are not available in SQL.

This includes procedural programming elements and a local variable to provide more flexible control of how the application flows. A number of functions were also added to T-SQL to make it more powerful; functions for mathematical operations, string operations, date and time processing, and the like. These additions make T-SQL comply with the Turing completeness test, a test that determines the universality of a computing language. SQL is not Turing complete and is very limited in the scope of what it can do.

Another significant difference between T-SQL and SQL is the changes done to the DELETE and UPDATE commands that are already available in SQL. With T-SQL, the DELETE and UPDATE commands both allow the inclusion of a FROM clause which allows the use of JOINs. This simplifies the filtering of records to easily pick out the entries that match a certain criteria unlike with SQL where it can be a bit more complicated.

Choosing between T-SQL and SQL is all up to the user. Still, using T-SQL is still better when you are dealing with Microsoft SQL Server installations. This is because T-SQL is also from Microsoft, and using the two together maximizes compatibility. SQL is preferred by people who have multiple backends.

References , Wikipedea , Tutorial Points :www.differencebetween.com

1. SQL or Structured Query Language was developed by IBM for their product "System R".

Later ANSI made it as a Standard on which all Query Languages are based upon and have extended this to create their own DataBase Query Language suits. The first standard was SQL-86 and latest being SQL:2016

2. T-SQL or Transact-SQL was developed by Sybase and later co-owned by Microsoft SQL Server.

3. PL/SQL or Procedural Language/SQL was Oracle Database, known as "Relation Software" that time.

I've documented this in my blog post.