Auto join suggestion

functional

Start 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.

SQLly
IntelliSense suggesting a JOIN ON clauseIntelliSense suggesting a JOIN ON clause
SQLly proposing an ON clause from a declared foreign key.

How it decides

  1. Find the tables already in the statement. Only the statement the caret is in counts; a new statement after ; or GO starts fresh.
  2. Follow foreign keys both ways. If Orders is in the query, SQLly offers the tables Orders points to (such as Customers) and the tables that point at Orders (such as OrderLines). Starting from FROM dbo.Customers c, it offers dbo.Orders o ON o.CustomerId = c.Id.
  3. Skip what is already joined. A table that is already in the statement is not offered again.
  4. Refer to existing tables the way you did. The ON clause 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 oSuggestions start with
nothing, or jJOIN
i, in, innerINNER JOIN
l, leftLEFT JOIN
r, rightRIGHT JOIN
f, o, full, outerFULL OUTER JOIN
anything elseJOIN, 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, then c2, c3.
  • Quoting: names are quoted the way your engine needs them. On SQL Server, a key into Order Details comes out as dbo.[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 ON clause yourself.
  • Self-referencing keys (such as Employees.ManagerId pointing to Employees.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.
Also on the right-click menu. Right-click a foreign-key column in the editor and choose Add foreign-key JOIN. SQLly adds a 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.