How to Use ChatGPT to Write SQL JOIN Queries

Updated on April 25, 2023:
– Added comparison of ChatGPT-3 and GPT-4 versions
– Added answers by GPT-4
– Presented an example demonstrating how to utilize ChatGPT-4 to create a query for a custom database

SQL JOINs are a fundamental aspect of relational database management and allow for the retrieval of data from multiple tables based on a specified condition. With the advent of advanced language models like OpenAI’s GPT, it is now possible to leverage their capabilities to write SQL JOIN queries efficiently and accurately.

In this article, we will explore the process of using ChatGPT to write SQL JOINs and provide a step-by-step guide on how to utilize AI for this purpose. We will be using dbForge Studio for SQL Server as a tool to verify and test SQL JOIN queries generated by ChatGPT. The Studio is a powerful database management solution that provides a user-friendly interface for executing SQL queries, making it easy to check the accuracy and efficiency of the queries generated by AI. Whether you’re a seasoned database developer or just starting to learn SQL, this article will provide valuable insights into the process of writing SQL JOINs with the help of the mentioned tools.

Contents

• ChatGPT-3 vs GPT-4
• How ChatGPT can help SQL developers
• Prerequisites
• Download and install the AdventureWorks2019 database
• Examples of SQL JOIN queries
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN
  • FULL OUTER JOIN
  • SELF JOIN
• Can ChatGPT learn?
• Conclusion 

ChatGPT-3 vs GPT-4

ChatGPT-4 was launched on March 14, 2023, and has been made publicly available in a limited form via ChatGPT Plus, with access to its commercial API being provided via a waitlist. While both models are designed to generate human-like text based on input, there are some key differences between them:

• Size and complexity: GPT-4 is larger and more complex than GPT-3. This means that it has a higher capacity to learn and generate text, which often results in improved performance in tasks like text completion, summarization, translation, and more.
• Training data: GPT-4 has been trained on more recent data than GPT-3, with a knowledge cutoff of September 2021, while GPT-3’s knowledge is limited to information up until June 2020. This enables GPT-4 to provide more up-to-date information and contextual understanding.
• Fine-tuning capabilities: GPT-4 has been designed with improved fine-tuning capabilities, allowing it to be more easily adapted to specific tasks or domains. This makes the model more versatile and useful in various applications.
• General performance: Due to its larger size and more recent training data, GPT-4 generally provides more accurate and relevant responses compared to GPT-3. It also tends to have a better understanding of context and is more adept at maintaining coherence across longer conversations.
• Comprehension and reasoning: While both GPT-3 and GPT-4 can generate human-like text, GPT-4’s increased complexity allows it to demonstrate better comprehension and reasoning abilities in certain situations.

How ChatGPT can help SQL developers

ChatGPT can assist SQL developers in several ways:

Writing SQL queries

ChatGPT can help write SQL queries for tasks such as data retrieval, data manipulation, and database management.

Debugging SQL code

ChatGPT can assist in troubleshooting SQL code by providing suggestions for optimizing query performance, fixing syntax errors, and identifying potential issues.

Providing information on SQL syntax

ChatGPT can provide information on SQL syntax, keywords, and functions, making it easier for developers to write correct SQL code.

Offering query optimization tips

ChatGPT can provide tips on how to optimize SQL queries, such as using indexes, reducing complexity, and avoiding subqueries.

Generating reports

ChatGPT can generate reports based on SQL data, such as sales reports, customer reports, and inventory reports.

Overall, ChatGPT can help SQL developers become more efficient and productive by providing quick answers and support for various SQL-related tasks. Let us look at how it can help with quite complex queries such as JOINs.

Prerequisites

To successfully replicate the scenarios demonstrated in this article, specifically using ChatGPT to compose SQL JOIN queries and validating them with dbForge Studio for SQL Server, the following prerequisites are required:

• dbForge Studio for SQL Server. This advanced database management tool will be used to verify and test the SQL JOIN queries generated by ChatGPT. You can download it here.
• ChatGPT Account. You will need an active account with OpenAI’s ChatGPT to interact with the language model and generate SQL JOIN queries.
• AdventureWorks2019 database. This sample database will be used to demonstrate the process of writing SQL JOIN queries with ChatGPT and then verifying them using dbForge Studio for SQL Server.

With these prerequisites in place, you will be ready to follow the steps outlined in this article and learn how to effectively use ChatGPT to write SQL JOIN queries and verify them using dbForge Studio for SQL Server.

Download and install the AdventureWorks2019 database

1. Download AdventureWorks2019.bak file from the official Microsoft website.
2. Restore the AdventureWorks2019 database with the help of a database management tool such as SQL Server Management Studio or dbForge Studio for SQL Server. To do that, open the tool, connect to your SQL Server instance, and then follow the steps to restore the database from a backup file.
3. Verify the database installation. Once the database has been restored, you can verify the installation by connecting to the database using the database management tool and browsing the database objects. You should see tables, views, stored procedures, and other database objects associated with the AdventureWorks2019 database.

Examples of SQL JOIN queries

Now that we have all the required prerequisites at hand, we can start using ChatGPT to write SQL JOIN queries and check them using dbForge Studio for SQL Server. Here’s how the process will work:

1. Interact with ChatGPT: We will use ChatGPT to create all the types of SQL JOINS by providing it with information about the desired result. This can be done through an interaction with the language model, where we will provide it with the necessary details about the tables and columns we want to join and the result we want to achieve.
2. Receive the SQL JOIN query: ChatGPT will generate the SQL JOIN query that meets the criteria specified. This query can be copied and pasted into dbForge Studio for SQL Server for execution.
3. Run the SQL JOIN query in dbForge Studio for SQL Server: Having created the query, we will use dbForge Studio for SQL Server to execute the query against the AdventureWorks2019 database. This will allow us to verify the results of the SQL JOIN query and make sure that it returns the result we require.

INNER JOIN

Let us start with the most widespread and simple JOIN — INNER JOIN. An inner join returns only the rows from both tables where there is a match on the specified join condition. In other words, it returns only the intersecting data of both tables, discarding all the unmatched rows.

Suppose, we want to get a list of all customers along with their addresses and the orders they have placed. Let’s ask ChatGPT to assist us.

Let’s now use dbForge Studio for SQL Server to execute the query that ChatGPT has created and confirm that it returns the results we require.

SELECT
  c.CustomerID
 ,p.FirstName + ' ' + p.LastName AS CustomerName
 ,a.AddressLine1
 ,a.city
 ,sp.Name AS StateProvince
 ,a.PostalCode
 ,o.SalesOrderID
 ,o.OrderDate
FROM Sales.Customer c
INNER JOIN Person.Person p
  ON c.PersonId = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress bea
  ON p.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Address a
  ON bea.AddressID = a.AddressID
INNER JOIN Person.StateProvince sp
  ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Sales.SalesOrderHeader o
  ON c.CustomerID = o.CustomerID

The query has been executed successfully, returning the desired result set.

Now, let’s pose the same question to ChatGPT-4 and compare the responses.

Here is the query generated by ChatGPT-4:

SELECT
  c.CustomerID,
  p.FirstName + ' ' + p.LastName AS CustomerName,
  a.AddressLine11,
  a.City,
  sp.Name AS StateProvince,
  a.PostalCode,
  o.SalesOrderID,
  o.OrderDate
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p
  ON c.PersonID = p.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress AS bea
  ON p.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Address AS a
  ON bea.AddressID = a.AddressID
INNER JOIN Person.StateProvince AS sp
  ON a.StateProvinceID = sp.StateProvinceID
INNER JOIN Sales.SalesOrderHeader AS o
  ON c.CustomerID = o.CustomerID
INNER JOIN Person.AddressType AS at
  ON bea.AddressTypeID = at.AddressTypeID
ORDER BY
  CustomerName,
  o.OrderDate;

Let’s test it and find out if it’s working as expected.

Excellent. The query is valid and yields the desired results. Let’s examine the queries generated by the two versions of ChatGPT more closely. Both queries fetch the same data; however, ChatGPT-4’s SELECT statement includes an extra table and orders the results by CustomerName and then by OrderDate in ascending order. This enhancement makes the query more informative and allows for easier analysis of the results.

LEFT JOIN

Let’s proceed to LEFT JOINS. Just a quick reminder — a LEFT JOIN in SQL is a type of join that returns all the rows from the left table (table1), and the matching rows from the right table (table2). If there is no match, NULL values will be output for the columns of the right table.

Now we will describe the desired result set to ChatGPT and ask it to write a query to get this result set. Suppose, we want to retrieve a list of all customers along with the number of orders they have ever placed.

As anticipated, ChatGPT has generated a query using a LEFT JOIN. Let’s execute it using dbForge Studio for SQL Server to verify that it provides the expected outcome.

SELECT
  c.CustomerID
 ,p.FirstName + ' ' + p.LastName AS CustomerName
 ,COUNT(o.SalesOrderID) AS NumberOfOrders
FROM Sales.Customer c
LEFT JOIN Person.Person p
  ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader o
  ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
        ,p.FirstName
        ,p.LastName

Next, let’s ask the same question of ChatGPT-4 and then compare the responses.

ChatGPT-4 generated the following query for us:

SELECT
  c.CustomerID,
  p.FirstName + ' ' + p.LastName AS CustomerName,
  COUNT(o.SalesOrderID) AS NumberOfOrders
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p
  ON c.PersonID = p.BusinessEntityID
LEFT JOIN Sales.SalesOrderHeader AS o
  ON c.CustomerID = o.CustomerID
GROUP BY
  c.CustomerID,
  p.FirstName,
  p.LastName
ORDER BY
  CustomerName;

Let’s validate the query in the Studio, shall we?

As demonstrated, the query has been executed successfully and produced the expected results. So, what are the primary distinctions between the queries proposed by ChatGPT-3 and its successor, ChatGPT-4?

The main difference between the two queries is the type of join used between the Customer and Person tables. The ChatGPT-3 uses a LEFT JOIN, while ChatGPT-4 uses an INNER JOIN. In ChatGPT-3’s query, a LEFT JOIN is used to join the Sales.Customer table with the Person.Person table. This means that all customers in the Sales.Customer table will be returned, even if they don’t have a corresponding record in the Person.Person table. In the ChatGPT-4’s query, an INNER JOIN is used between the Sales.Customer and Person.Person tables, which means that only the customers with a matching record in the Person.Person table will be returned. Additionally, ChatGPT-4 adds an ORDER BY clause at the end of its query to sort the results by the CustomerName column in ascending order, making the result set easier to perceive and analyze.

RIGHT JOIN

Let us now ask ChatGPT to write a SELECT query with a RIGHT JOIN. A RIGHT JOIN in SQL works similarly to the LEFT JOIN and returns all the rows from the right table (table2) and the matching rows from the left table (table1). If there is no match, NULL values will be returned for the columns of the left table.

Suppose that we want to retrieve a result set that displays all vendors and all purchases made from these vendors in the AdventureWorks2019 database.

And again, let’s run the query created by ChatGPT in dbForge Studio for SQL Server — just to make sure it is working and returns the result set we expect.

SELECT
  v.Name AS VendorName
 ,p.OrderDate
 ,p.TotalDue
FROM [Purchasing].[PurchaseOrderHeader] p
RIGHT JOIN [Purchasing].[Vendor] v
  ON v.BusinessEntityID = p.VendorID

Now, let’s request ChatGPT-4 to generate a query that will output the same result.

The AI provided us with the following SELECT statement:

SELECT 
    V.BusinessEntityID AS VendorID,
    V.Name AS VendorName,
    POH.PurchaseOrderID,
    POH.OrderDate,
    POH.TotalDue
FROM 
    Purchasing.PurchaseOrderHeader POH
RIGHT JOIN 
    Purchasing.Vendor V ON POH.VendorID = V.BusinessEntityID
ORDER BY
    V.BusinessEntityID, POH.PurchaseOrderID;

Let’s check if the generated query is working correctly.

The two queries are similar in that they join the same two tables, Purchasing.PurchaseOrderHeader and Purchasing.Vendor, using a RIGHT JOIN, and retrieve some of the same columns, such as VendorName and OrderDate. However, there are some differences. ChatGPT-4 includes VendorID and PurchaseOrderID in the output, which are not present in the query created by ChatGPT-3. ChatGPT-4’s also includes an ORDER BY clause, ordering the results first by VendorID and then by PurchaseOrderID, which makes the results easier to read and understand.

FULL OUTER JOIN

FULL OUTER JOIN is a type of join operation in relational databases where all rows from both tables being joined are included in the result set, regardless of whether there is a match in the other table. If there is a match, the rows from both tables are joined together. If there is no match, NULL values are used to fill in the columns from the missing table.

Let us ask ChatGPT to assist us with the FULL OUTER JOIN query.

And now we check the query in dbForge Studio for SQL Server as we have done in previous examples.

SELECT
  *
FROM Sales.Customer
FULL OUTER JOIN Sales.SalesOrderHeader
  ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID;

CROSS JOIN

A CROSS JOIN, also known as a cartesian product, is a type of join operation in a relational database that combines every row from one table with every row from another table. The resulting table contains all possible combinations of rows from both tables. Let us ask ChatGPT to create a query to get all possible combinations of product categories and subcategories in the AdventureWorks2019 database.

Let us now run the query in dbForge Studio for SQL Server to validate it.

SELECT
  pc.Name AS CategoryName
 ,psс.Name AS SubcategoryName
FROM Production.ProductCategory pc
CROSS JOIN Production.ProductSubcategory psс

Let us now approach ChatGPT-4 with the same question.

So, we got the following query:

SELECT 
    PC.ProductCategoryID,
    PC.Name AS ProductCategoryName,
    PSC.ProductSubcategoryID,
    PSC.Name AS ProductSubcategoryName
FROM 
    Production.ProductCategory PC
CROSS JOIN 
    Production.ProductSubcategory PSC
ORDER BY
    PC.ProductCategoryID, PSC.ProductSubcategoryID;

And now as you might have guessed, we will check the query ChatGPT-4 has created in dbForge Studio for SQL Server.

Let us compare and analyze the queries created by ChatGPT-3 and ChatGPT-4. Both of them use a CROSS JOIN to generate all possible combinations of product categories and subcategories. The main difference is in the selected columns and the presence of an ORDER BY clause in the query written by ChatGPT-4 to sort the result set. While ChatGPT-3 provided us with the query that returns a more concise result set, ChatGPT-4’s query is more informative and ensures a sorted output for better analysis.

SELF JOIN

In SQL, a SELF JOIN is a regular join operation where a table is joined with itself. It is useful when we want to compare the data within a single table.

So, to receive a SELF JOIN, we need to ask ChatGPT to join a table to itself as if the table were two tables. Suppose we want obtain a list of all supervisors and their subordinates from the HumanResources.Employee table in the AdventureWorks2019 database, where the supervisor and subordinate share the same gender. Let us ask AI for help, shall we?

Great! The query is ready. Let’s now validate it in dbForge Studio and review the output.

SELECT 
    e1.Gender AS Gender,
    e1.JobTitle AS Supervisor,
    e2.JobTitle AS Subordinate
FROM 
    HumanResources.Employee e1
    FULL JOIN HumanResources.Employee e2
        ON e1.Gender = e2.Gender
WHERE 
    e1.OrganizationLevel = 1
    AND e2.OrganizationLevel <> 1

Well, now we are going to give the same task to ChatGPT-4.

ChatGPT-4 presented the following query:

SELECT 
    S.BusinessEntityID AS SupervisorID,
    S.JobTitle AS SupervisorJobTitle,
    E.BusinessEntityID AS SubordinateID,
    E.JobTitle AS SubordinateJobTitle,
    E.Gender
FROM 
    HumanResources.Employee E
JOIN 
    HumanResources.Employee S ON E.OrganizationNode.GetAncestor(1) = S.OrganizationNode
WHERE 
    E.Gender = S.Gender
ORDER BY
    S.BusinessEntityID, E.BusinessEntityID;

As with the previous examples, let’s validate the query in dbForge Studio.

As anticipated, the query is valid. But what is the difference between the ChatGPT-3’s and ChatGPT-4’s answers? ChatGPT-4 determines the supervisor-subordinate relationship using the OrganizationNode hierarchy and the GetAncestor(1) function. It also selects more columns and uses the ORDER BY clause to sort the result set by SupervisorID and SubordinateID.

The comparative analysis of the queries with JOINs created by ChatGPT-3 and ChatGPT-4 demonstrates that the latter is designed to yield more informative result sets, as it incorporates additional columns to enhance the comprehensiveness of the results. Unlike ChatGPT-3, ChatGPT-4 consistently includes an ORDER BY clause, which makes the returned result set more organized and easier to analyze, facilitating the identification of data patterns.

Can ChatGPT learn?

ChatGPT is a machine learning-based language model that is designed to learn and improve over time. It is trained on vast amounts of text data and uses that data to generate responses to user inputs. As ChatGPT interacts with users and receives feedback, it can continue to learn and adapt to user needs, improving its responses and becoming more accurate and effective over time.

When a user provides more context or details about their question or topic, ChatGPT can use that information to generate more relevant and accurate responses. By incorporating user feedback and additional information into its input data, ChatGPT can learn and adapt to the user’s needs, and generate more effective answers.

In this article, we explored a well-known sample database. You might wonder how to implement ChatGPT in your own project. Let’s examine how ChatGPT can make decisions based on a given context. We will provide the database script and then ask the AI to compose a query to retrieve the desired data. Ready to begin?

Suppose we have an Olympic database containing information on athletes, the medals they’ve won, the matches they’ve participated in, and more. Here is the script for this database, without any actual data:

CREATE DATABASE Olympic;

CREATE TABLE country
(
  cname varchar(50) not null unique
  ,total_medal_number INTEGER
  ,gold_number INTEGER 
  ,silver_number INTEGER
  ,bronze_number INTEGER
  ,PRIMARY KEY(cname)
  ,CHECK (gold_number>=0 and silver_number>=0 
  	and bronze_number>=0 and total_medal_number>=0)
);

CREATE TABLE player
(
   player_id INTEGER NOT NULL unique
  ,pname VARCHAR(50) NOT NULL
  ,gender VARCHAR(50) NOT NULL
  ,cname VARCHAR(50) NOT NULL
  ,gold_number INTEGER NOT NULL -- If no medals, then it should be 0
  ,silver_number INTEGER NOT NULL
  ,bronze_number INTEGER NOT NULL
  ,PRIMARY KEY(player_id)
  ,FOREIGN KEY(cname) REFERENCES country(cname)
  ,CHECK (player_id>=0 and gold_number>=0 and silver_number>=0 and bronze_number>=0)
);

CREATE TABLE sports
(
  sports_id INTEGER NOT NULL
  ,stype varchar(50) NOT NULL
  ,sname VARCHAR(50) NOT NULL
  ,distance INTEGER
  ,team_type VARCHAR(50) NOT NULL
  ,gender VARCHAR(50) NOT NULL
  ,UNIQUE (sname, distance, team_type, gender) 
  ,PRIMARY KEY(sports_id)
  ,CHECK (distance = 100 or distance = 200 or distance = 400 or distance = 800 or distance = 1000 or distance = 1500)
  ,CHECK(gender = 'male' or gender = 'female')
  ,CHECK(stype = 'Swimming' or stype = 'Athletics')
  ,CHECK(team_type = 'Single' or team_type = 'Team')
);

CREATE TABLE  matches
(
   match_id INTEGER NOT null unique
  ,match_type VARCHAR(50)
  ,location VARCHAR(50) default 'Water Cube'
  ,match_date date not null
  ,sports_id INTEGER NOT null
  ,PRIMARY KEY(match_id)
  ,foreign KEY(sports_id) references sports(sports_id)  on delete cascade
  ,CHECK (match_type = 'final' or match_type = 'semi-final' or match_type = 'preliminary')
  ,CHECK (match_id >= 0)
);

CREATE TABLE participate
(
  player_id INTEGER not null
  ,match_id INTEGER not null
  ,result varchar(50) not null
  ,ranking INTEGER not null
  ,medal_type VARCHAR(50)
  ,PRIMARY KEY(player_id, match_id)
  ,foreign key(player_id) REFERENCES player(player_id)
  ,foreign key(match_id) REFERENCES matches(match_id)
  ,CHECK (medal_type = 'gold' or medal_type = 'silver' or medal_type = 'bronze')
  ,CHECK (ranking>=0 and player_id>=0 and match_id>=0)
);

CREATE TABLE spectator
(
   sid INTEGER NOT NULL unique
  ,sname varchar(50)
  ,PRIMARY KEY(sid)
);

CREATE TABLE ticket
(
   tid INTEGER
  ,match_id integer NOT null
  ,sid integer NOT null
  ,seat_number varchar(50) NOT NULL
  ,price float NOT NULL
  ,PRIMARY KEY(tid)
  ,foreign key(match_id) references matches(match_id)
  ,foreign key(sid) references spectator(sid)
  ,CHECK (tid>=0 and sid>=0 and match_id>=0 and price>=0.0)
);

So, we’ve provided ChatGPT-4 with the database script and asked it to create a query that retrieves a list of athletes, their gender, and the number of medals they have won. Here is the response we received:

Here is the query we got:

SELECT
  p.pname AS "Sportsman"
 ,p.gender AS "Gender"
 ,p.gold_number + p.silver_number + p.bronze_number AS "Total Medals"
FROM player p
ORDER BY "Total Medals" DESC;

Now, let us open dbForge Studio for SQL Server and check the generated query.

As you can clearly see, the query is valid and returns the desired result set. This demonstrates that AI can generate queries not only for sample databases but also for custom scenarios when given the appropriate context. This opens up limitless possibilities for developers, analysts, DBAs, and even individuals who may not be experts in SQL but still need or want to work with databases.

Conclusion

The article demonstrates that even if you are not an experienced SQL developer, you can still create complex queries involving JOINs. All you need to do is explain to the AI in detail the type of result you want to obtain or which tables to join, and it will take care of the task for you. It’s remarkable, isn’t it? As a result, complex database analysis and development are now becoming more accessible to the general public.

As a language model, ChatGPT can provide guidance and suggestions on how to construct complex SQL queries and JOINs, but it does not have direct access to a SQL database. It can help you with syntax, best practices, and general guidance on how to structure your queries for efficient execution. However, to create and run SQL queries of any complexity, you would need to use a database management system (such as SQL Server) and a GUI client to interact with this DBMS (such as dbForge Studio for SQL Server). If you are interested in how ChatGPT can help you write complex MySQL statements, feel free to read the Power Up Your MySQL Queries: How ChatGPT Can Help You Retrieve MySQL Data article.

If you are looking for a powerful and user-friendly database management tool, we recommend giving the Studio a try. With features like code completion, query profiling, and a visual query builder, it can significantly streamline your SQL Server development process. You can download a free trial of dbForge Studio for SQL Server from the Devart website to see how it can improve your workflow.