Confused Between SQL, T-SQL, and PL/SQL? Here’s the Clear Difference"

 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. 

🎁 My new book, Grokking the SQL Interview, is here!!

 Hello guys,

I am feeling proud and excited to announce the release of my new book, "Grokking the SQL Interview". Yes, it's finally available now after 2 years of waiting and you can purchase it now on Gumroad for a special price of just $10.95 (original price $21.90).

I thought about this book 2 years ago, and it took really long to work on this as I tried to make it as perfect as possible.

Here is the first tweet I made about this book 2 years ago for remember:

Now, I can proudly say that I have finally completed it and it's ready for purchase and reading, a big moment for me !!.

The book follows the same format as my previous Grokking the Java Interview and Grokking the Spring Boot Interview and tries to teach you SQL by interview questions. This is primarily for people who want to prepare for SQL questions like programmers, data scientist, IT support, BA, QA, or anyone who need to answer SQL questions in interviews.

It covers the following topics:

This book contains frequently asked Java questions from essential topics like

1. SQL and Database Phone interview questions

2. Joins in SQL

3. SQL Query Questions

4. Indexes

5. Group by and Aggregation

6. SQL Date and Time Questions

7. Stored Procedures

8. Triggers and Views

9. Transactions

10. Window Function and CTE

11. Deep Dive on popular SQL Questions

You can see that the topic has expanded since my first twee, as I tried to cover a few more things to make it complete.

For now, you can grab your copy for a big 50% discount,

Here is the link - 50% OFF on Grokking the SQL Interview

I really hope that this book helps you in your journey of cracking your next developer interview.

All the best

Javin

2 Ways to remove duplicate rows in SQL? Example Tutorial

Hello guys, if you are wondering how to remove duplicate rows in SQL and looking for solutions on different databases like SQL Server, MySQL, and Oracle then you have come to the right place. In this article, I am going to share 2 ways to remove duplicate rows from SQL, first by using GROUP BY and HAVING clause and second by using the RANK function which works on most of the databases.  You can use any of the approaches to remove duplicates from tables using SQL. This is also one of the popular SQL Interview Questions so knowing how to solve this problem will also give you an edge on interviews. 

Top 30 MySQL Interview Questions for 2 to 3 Years Experienced Developers

Hello guys, if you are preparing for MySQL interviews or you are preparing for a tech interviews where MySQL skills are needed and you are looking for MySQL questions then you have come to the right place. Earlier, I have shared 15 SQL queries from interviews and 30 Database Interview Questions and in this article, I am going to share 50+ MySQL questions which you can practice before interviews. These MySQL question touches many different topics on MySQL like Database and SQL related as well MySQL engine specific questions like MyISAM vs InnoDB etc.

How to find Find all tables containing column with specified name - MS SQL Server

Hey there! Today, we're going on a quest to uncover one of SQL Server's well-kept secrets: how to find all the tables containing a column with a specified name. Don't worry; this isn't a treasure hunt involving maps and riddles – just a few SQL queries and a sense of curiosity similar to how to find Nth highest salary we have seen earlier. 

Have you ever found yourself lost in a vast SQL Server database, wondering where a particular column is hiding? Or perhaps you need to perform some maintenance tasks on all tables with a specific column name? Well, you're in luck, because I'm here to guide you through this adventure.

How to find Find all tables containing column with specified name - MS SQL Server Example

Have you ever found yourself in a labyrinthine SQL Server database, desperately searching for that one elusive column with a specific name? If so, you're in the right place. Today, I'm going to be your guide on a thrilling quest to unveil the secrets of SQL Server – how to find all the tables containing a column with a specified name.

This adventure isn't about maps, compasses, or treasure chests, but rather about SQL queries and a knack for exploration. So, grab your digital magnifying glass, dust off your SQL knowledge, and let's embark on this thrilling journey to uncover the hidden gems within your SQL Server databases.

Imagine you're in charge of a colossal SQL Server database with countless tables and columns, and you've been tasked with identifying all the tables that house a column named 'customer_id.' It's like searching for a needle in a colossal data haystack, isn't it? Fear not, my friend, because we're about to shed light on this intriguing SQL riddle.

Introduction

In the world of SQL Server, where databases can be vast and complex, locating a specific column can feel like navigating a maze blindfolded. But worry not; SQL Server provides us with tools and techniques to tackle this challenge head-on.

Whether you're a database administrator, a developer, or just a curious explorer of data, the ability to find tables containing a specific column can be a game-changer. So, let's unravel this mystery together and equip you with the skills to become a proficient SQL detective.

Are you ready to embark on this SQL adventure? Great! Let's dive right in.

Approach 1: Using SQL Server Management Studio (SSMS)

Let's start with the most user-friendly method. If you're a fan of graphical user interfaces, SQL Server Management Studio (SSMS) is your trusty sidekick. Here's how you can find all tables with a specific column using SSMS:

Open SQL Server Management Studio: Fire up SSMS and connect to your SQL Server instance.

Object Explorer: In the Object Explorer pane on the left, expand your database to reveal its tables.

Search for the Column: Right-click on the Tables folder and select "Filter" -> "Filter Settings." In the "Filter Settings" dialog, choose the "Columns" tab, and then click "Add Filter."

Specify Column Name: In the "Filter Column" dialog, select the "Name" column and set the operator to "Contains." Enter the name of the column you're looking for, such as 'email.' Click "OK" to apply the filter.

Browse the Results: Voila! You'll now see a list of tables that contain a column with the name 'email.' You can right-click on these tables to perform various actions.

While SSMS is user-friendly, keep in mind that this method is limited to one database at a time. If you need to search across multiple databases or automate the process, we'll explore more SQL-based methods next.

Approach 2: Using SQL Queries

Now, let's get our hands dirty with some SQL queries. This approach offers more flexibility and can be automated for larger tasks.

Method 1: Information Schema

SQL Server provides a special system view called information_schema.columns that stores information about columns in all tables within a database. We can query this view to find tables containing our desired column. Here's a sample SQL query:

SELECT TABLE_NAME
FROM information_schema.columns
WHERE COLUMN_NAME = 'email';

This query retrieves the names of all tables that have a column named 'email' in the current database. You can adapt it to search in specific databases by changing the information_schema.columns reference accordingly.

Method 2: Using sys.tables and sys.columns

Another way to tackle this is by using the system views sys.tables and sys.columns provided by SQL Server. These views give you more control over the information you retrieve. Here's a query using this method:

SELECT t.name AS table_name
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'email';

This query joins the sys.tables and sys.columns views to list the names of tables that contain a 'email' column. It's slightly more verbose but offers greater flexibility.

Conclusion

Congratulations! You've just mastered the art of finding tables with specific columns in SQL Server. Whether you prefer the user-friendly environment of SQL Server Management Studio or the flexibility of SQL queries, you now have the skills to unearth hidden database treasures.

Remember, SQL is all about exploration and problem-solving, so don't hesitate to apply these techniques to real-world scenarios. With a bit of curiosity and the right SQL tools, you can navigate even the most labyrinthine databases. Happy SQL hunting!

Other Related Articles

10 Ways to use GROUP BY command in SQL With Examples

10 ways to use SELECT command in SQL With Examples

How to remove duplicate values from a table? Example

FAQ: Your Burning Questions Answered

Q1: Can I search for columns across all databases on my SQL Server instance?

Yes, you can. To search across all databases, you'll need to create a dynamic SQL query that iterates through the databases on your server. It's a bit more advanced but doable.

Q2: What if I want to find columns with similar names, like 'email' and 'e-mail'?

For such cases, you can modify your query to use wildcard characters in the WHERE clause. For example, WHERE COLUMN_NAME LIKE '%email%' would find columns with 'email' anywhere in their names.

Q3: Is there a way to retrieve additional information about these tables, like schema names or data types of the columns?

Absolutely! You can expand your query to include more columns from the information_schema.columns view or join other system views to retrieve additional information.

Q4: Can I automate this search and export the results to a file or another database?

Yes, you can. You can use SQL Server's SQLCMD utility or PowerShell scripts to automate this task, export the results, and perform further actions.

Q5: What if I'm working with SQL Server versions older than 2005?

While the methods described here should work for most SQL Server versions, some older versions may require slight variations in the SQL queries. Be sure to check your version's documentation for any specific differences.

Quiz Time: Test Your SQL Explorer Skills

Let's put your SQL exploration skills to the test with a quick quiz:

1. What is the primary advantage of using SQL queries to find tables with specific columns?

a) It's quicker than using SQL Server Management Studio.

b) It provides more flexibility and can be automated.

c) It doesn't require any prior SQL knowledge.

2. Which SQL system view stores information about columns in all tables within a database?

a) sys.tables

b) sys.columns

c) information_schema.columns

3. How can you search for columns with similar names, such as 'email' and 'e-mail'?

a) Modify the SQL Server Management Studio filter settings.

b) Use wildcard characters in the SQL query's WHERE clause.

c) Manually inspect each table's columns.

Difference between Equi Join and Non-Equi Join in SQL with Examples

Hello guys, If you want to learn about the difference between Equi and Non-Equi join in SQL then you have come to the right place. Earlier, I have shared the difference between LEFT and RIGHT join in SQL and now we will understand equi and non-equi join in SQL and database. The term "Non-Equi join" in SQL Server may sound abstract, but it's far from it. Equi joins are the same way. I think you'll agree with me after reading this essay. I'll attempt to explain what they are and when they should be used today. So, let's get this party started. 

10 Example of SubQuery in SQL

Hey there! Today, we're diving headfirst into the fascinating world of SQL subqueries. As someone who's spent countless hours exploring databases, almost 24 years now, I'm excited to show you how these versatile subqueries can supercharge your SQL game. Along with Joins, subquery is one of the must know concept for SQL developers. It's also slightly easier to understand then join, especially non-correlated subquery. Correlated subquery is  slightly harder to understand but don't worry these 10 example will ensure you know subquery well. 

How to find Nth Highest Salary of Employee in SQL? Example Tutorial

The Problem description

Finding the Nth highest salary of workers, where N might be 2, 3, 4, or anything, is one of the most typical SQL interview questions. This query is sometimes rephrased as "find the nth minimal wage in SQL." Many programmers struggle to design the SQL query when the Interviewer keeps asking for the 4th highest, 5th highest, and so on since they only know the easiest approach to address this problem, like by utilizing the SQL IN clause, which doesn't scale well. 

Difference between Correlated and Non-Correlated SubQuery in SQL

What is difference between correlated and non-correlated subquery in SQL?

A correlated subquery in SQL is a subquery that is dependent on the outer query. This means that the subquery relies on data from the outer query to provide a result. The subquery is executed for each row in the outer query and its result is used in the WHERE clause of the outer query. While, a non-correlated subquery, on the other hand, is independent of the outer query and is executed only once. The result of the subquery is stored in a temporary table, which is then used by the outer query.

How do I perform an IF...THEN in an SQL SELECT?

Hey there! Today, I'm going to take you on a journey through the fascinating world of SQL, and we're going to dive headfirst into the intriguing realm of the IF...THEN statement within an SQL SELECT query. Don't fret; it's not as complicated as it sounds. So, grab your favorite coding beverage, settle in, and let's unravel this SQL mystery together.

How to import an SQL file using the command line in MySQL?

Hey there! Today, I want to take you on a journey through the world of MySQL and show you how to import an SQL file using the good old command line. It might sound intimidating, but I promise it's not rocket science. So, grab your favorite beverage, fire up your terminal, and let's dive right in.

How do I UPDATE from a SELECT in SQL Server?

Hey there! Today, I want to dive into a handy SQL Server trick that has saved me time and effort countless times: updating data in one table based on the results of a SELECT query. It's a bit like magic, and I'm here to share the spell with you. So, grab your wizard hat, and let's get started!

10 Examples of Creating index in SQL

 Hey! Today, I'm thrilled to dive deep into the world of SQL indexing, where a well-placed index can supercharge your database queries. Think of indexing as creating a map that helps SQL quickly find the treasures (data) you seek. In this article, I'll guide you through ten real-world scenarios where indexing becomes your trusty sidekick.

10 Examples of JOIN Clause in SQL

 Hey! Ready to embark on an exciting journey through the world of SQL JOIN? I'm here to guide you through ten real-world scenarios where the JOIN clause becomes your trusty sidekick, helping you merge data from different tables effortlessly.

Top 10 MySQL functions Every programmer should learn

 Hey! If you're venturing into the realm of MySQL, you're in for a treat. MySQL functions are like the secret spells in a wizard's grimoire, capable of transforming your code into pure magic. In this article, I'll be your magical guide as we explore the top 10 MySQL functions that every programmer should learn. So, grab your enchanted staff, and let's conjure some MySQL wizardry together!

Top 10 PL/SQL Functions Every Programmer should Learn

 Hey there! If you're diving into the world of Oracle databases and PL/SQL, you're in for a treat. PL/SQL functions are like the secret spices in a recipe; they can transform your code and supercharge your applications. In this article, I'm excited to share my top 10 PL/SQL functions that every programmer should learn. So, grab your coding apron, and let's spice up our PL/SQL dishes together!

Top 15 SQL Query Interview Questions for Practice (with Solutions)

Hello folks, if you are preparing for technical interviews for software developer, system admin, data analyst, or data scientist, one skill you must prepare is SQL. It's one of the important topic for programmers, support engineers and DBAs as you have to deal with SQL in your day to day job. If you don't know, SQL is the declarative language that allows you to interact with the database. By using SQL you can insert, update, search, and delete data in a relational database. The code you write is called SQL queries and it's one of the essential skills for many technical people who have to deal with databases. That's why SQL queries are very popular in interviews and you will often see one or more SQL queries on interviews. 

5 Ways to find the Second Highest Salary of Employee in SQL [Solved]

 Hello guys, if you have attended programming or software development interview then you may have come across SQL queries. In the past, I have shared popular SQL queries from interviews and one of them is how to find the 2nd highest salary of employee in SQL. This is probably the most popular SQL query question I have come across in my career and I have seen it many times. When I first come across this one I have hard time to solve it but over the years, I have learned several ways to solve this problem and find the 2nd highest or even 3rd highest salary in SQL. Earlier, I have shared how to find Nth highest salary and in this article, I am going to share 5 different ways to find the second highest salary in SQL. 

Difference between JOIN and SUBQUERY in SQL

Hey there! Ever found yourself at the crossroads of JOIN and SUBQUERY, wondering which path to take in your database adventures? Well, you're not alone! Today, I'm here to help you decipher the intriguing differences between these SQL techniques.

Difference between ISNULL and COALESCE in SQL Server

Hey there! Today, I'm diving deep into the fascinating world of SQL Server to unravel the mysteries of two essential functions: ISNULL and COALESCE. You've probably encountered these in your SQL adventures, but do you know when to use one over the other? Join me as I break it down.