Is there an alternative to joins to increase performance?

Question

Is there an alternative to joins to increase performance?

Edit (gbn): related to join-or-correlated-subquery-with-exists-clause-which-one-is-better

---

Why didn't anyone mention about nested loop joins?

Answer 1

Not an "alternate" way to JOINs, but a tip to increase JOIN performance: in SQL Server that a lot of folks don't know is that you should always put a non-clustered index on a foreign key column. Several folks believe SQL Server does this automatically - it does not.

So if you have a table Customer, it probably has a primary key something like CustomerID. SQL Server will put an index on that automatically.

However, if you have a table Order that has a foreign key relationship with Customer, there is by default no index on the column Order.CustomerID. But such an index is very useful and helpful for joins and lookups, so that's a best practice I always recommend: put an index on all your foreign key columns in a table.

Answer 2

From your other question

select * 
from ContactInformation c 
where exists (select * from Department d where d.Id = c.DepartmentId )

select * 
from ContactInformation c 
inner join Department d on c.DepartmentId = d.Id  

If you want output from both tables, then you have option other then JOIN. The 2nd query here.

If it's slow, then generally:

• you have primary key/indexes?
• consistent datatypes (the DepartmentId/id columns)
• don't use SELECT *