Auto join suggestion
functionalStart a JOIN and SQLly offers the ON clause, built from the foreign keys your schema declares.
Type JOIN after a table and SQLly can offer the whole join in one
suggestion: the related table, a short alias, and a ready-to-run ON
clause. It works from the foreign keys in your schema, so the
columns it pairs are the ones the database actually relates, not a guess based on
names.
What you see
Suppose dbo.Orders has a foreign key from CustomerId to
dbo.Customers.Id. With the caret after JOIN:
SELECT * FROM dbo.Orders o JOIN ▮
the list offers dbo.Customers ON Id = o.CustomerId, marked
fk. Accept it and SQLly writes:
SELECT * FROM dbo.Orders o JOIN dbo.Customers c ON c.Id = o.CustomerId
Typing part of a table name after JOIN narrows the list to matching
tables. Join suggestions are ranked near the top. An ordinary table that is related
to one already in the query also gets a small boost and the label
related via FK.


How it decides
- Find the tables already in the statement. Only the statement the caret is in counts; a new statement after
;orGOstarts fresh. - Follow foreign keys both ways. If
Ordersis in the query, SQLly offers the tablesOrderspoints to (such asCustomers) and the tables that point atOrders(such asOrderLines). Starting fromFROM dbo.Customers c, it offersdbo.Orders o ON o.CustomerId = c.Id. - Skip what is already joined. A table that is already in the statement is not offered again.
- Refer to existing tables the way you did. The
ONclause uses your alias for the existing side. With no alias, it uses the full name:JOIN Sales.Customers c ON c.CustomerId = Sales.Orders.CustomerId.
When you have already written the table
If you type the table and its alias yourself, SQLly still finishes the join. With the
caret right after the alias and no ON yet:
SELECT * FROM Accounting.Account AS a JOIN Accounting.FinancialAccountingKey AS fak ▮
the list offers only that table's join conditions, each one starting with the
ON keyword, such as ON fak.AccountId = a.AccountId, plus a bare
ON if you would rather write the condition yourself. There is one condition
for each foreign key the table shares with any earlier table in the query, and
the nearest earlier table comes first. Joins to other tables are not offered here,
because you have already chosen the table.
The same happens after you type ON and a space. When foreign keys link the
joined table to earlier tables, the list offers only those full conditions - no tables
and no loose columns - with the joined table's alias written first and the nearest
earlier table preferred. When no key links them, you get the columns instead, so you can
write the condition by hand. A composite key is still one suggestion joined with
AND.
Starting the join keyword for you
You don't have to type JOIN first. Right after a table in
FROM, a short prefix picks the join type and every foreign-key join is
offered with it:
You type after FROM dbo.Orders o | Suggestions start with |
|---|---|
nothing, or j | JOIN |
i, in, inner | INNER JOIN |
l, left | LEFT JOIN |
r, right | RIGHT JOIN |
f, o, full, outer | FULL OUTER JOIN |
| anything else | JOIN, filtered by table name |
Aliases and quoting
- Alias: the joined table gets the first letter of its name in lower case. If that alias is already used in the statement, a number is added:
c, thenc2,c3. - Quoting: names are quoted the way your engine needs them. On SQL Server, a key into
Order Detailscomes out asdbo.[Order Details] o2 ON o2.[Order ID] = o.Id. MySQL and MariaDB use backticks, and PostgreSQL and Oracle use double quotes.
Edge cases
- No foreign key: no join is suggested. SQLly does not guess relationships from column names here. If your schema has no declared keys, you still get ordinary table completion, and you write the
ONclause yourself. - Self-referencing keys (such as
Employees.ManagerIdpointing toEmployees.Id) aren't offered as joins, because the table is already in the query. Write self-joins by hand. - Multi-column keys: a composite foreign key is one suggestion that joins every column pair with
AND, in the key's column order:JOIN dbo.Orders o ON o.TenantId = ol.TenantId AND o.OrderNo = ol.OrderNo. Two separate keys to the same table, such as a billing and a shipping address, are still two suggestions. - After
ON: the list offers the joined table's full foreign-key conditions when there are any, and columns only when no key links the joined table to an earlier one. See When you have already written the table above.
LEFT JOIN to the referenced table. If that table has a Name column, Show referenced display name also swaps the key column for that name.