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.
get customers with their orders
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.
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:
get customers with their orders even if no order match
And it outputs:
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.
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).
Since are uncommon, you need to specify it.
get customer right joined with orders
And it outputs:
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.
AI is asked to:
get customers FULL JOIN orders
And it outputs:
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.