Generate SQL JOIN Queries With AI

JOINs are common SQL queries that enables users to retrieve data from multiple different tables and combine them with one query. Without having seen any statistics a qualified guess would be that 99% of JOINs are either (INNER) JOIN or LEFT JOIN. That doesn't leave much room for the odds ones, more on them later.

To make the JOIN queries we use 2 tables a customers table and orders table. The customers table has 3 columns: customerNumber (primary key), customerName and country. The orders table has 3 columns: orderNumber (foreign key), orderDate and customerNumber. To build your own you can just reuse the input below and replace the table names with your own. Or add additional clauses to suit your needs.

JOIN (also known as INNER JOIN)

If anything else isn't specified, AI will usually suggest this JOIN.

            

              Copy
            
            get customers with their orders
          

            

              Copy
            
            SELECT
  *
FROM
  customers c
  JOIN orders o ON c.customerNumber = o.customerNumber;
          

This will return only the customers who has orders. If a customer has no order(s), it will not be returned.

Snippet link.

LEFT JOIN

The LEFT JOIN differs in that it will return all customers even if there are no orders. With the "...even if no order match" part, AI Bot understands that:

            

              Copy
            
            get customers with their orders even if no order match
          

And it outputs:

            

              Copy
            
            SELECT
  *
FROM
  customers c
  LEFT JOIN orders o ON c.customerNumber = o.customerNumber;
          

This will return all customers and any order they have. If a customer has no orders, it will return the customer with a NULL order.

Snippet link.

The odd ones

While writing this I noticed that it was difficult getting AI Bot to generate a RIGHT JOIN or FULL JOIN query. I tried changing the different OpenAI parameters like temperature to see if my settings where too restrictive. Long story short: to have AI Bot return either RIGHT JOIN or FULL OUTER JOIN, you need to specify it. AI Bot won't guess (unless you outright write it) that you would prefer any of those uncommon queries.

This is properly because they are so rarely used, partly unsupported, and considered bad practice. OpenAI therefore predicts that the likelihood that the user actually wants a RIGHT JOIN or FULL OUTER JOIN is too small to suggest, unless directly specified.

On a side note not all databases support RIGHT JOIN (not supported by SQLITE) or FULL JOIN (not supported by MYSQL).

RIGHT JOIN

Since are uncommon, you need to specify it.

            

              Copy
            
            get customer right joined with orders
          

And it outputs:

            

              Copy
            
            SELECT
  *
FROM
  orders
  LEFT JOIN customers ON orders.customerNumber = customers.customerNumber
          

This query is bad practice and has few if any use cases. It could be re-written as LEFT JOIN with the customers and orders changing places: get orders with their customers even if no customer match.

FULL JOIN

AI is asked to:

            

              Copy
            
            get customers FULL JOIN orders
          

And it outputs:

            

              Copy
            
            SELECT
  *
FROM
  customers
  FULL JOIN orders ON customers.customerNumber = orders.customerNumber;
          

This will return all customers and their orders. If a customer has no orders, it will return the customer with a NULL order. If an order has no customer, it will return the order with a NULL customer.