Is there a better way to get all distinct values from three columns in one table other than using the DISTINCT function? I've also tried GROUP BY, but there doesn't seem to be any noticeable difference in the cost.
SELECT DISTINCT Table1.Col1, Table2.Col1, Table1.Col3
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID
WHERE Table1.Foo = 1865 AND Table2.Type = 1
No, there's not, but if you're struggeling with performance on this, you might want to consider indexes. If you provide more details, maybe we can help with this
Nope, that's how it's done.
Although, you could try:
SELECT DISTINCT Table1.Col1, Table2.Col2
FROM Table1
INNER JOIN Table2 ON Table1.FK = Table2.ID AND Table2.Type = 1
WHERE Table1.Foo = 1865
Speed will depend on your data.
Also see http://stackoverflow.com/questions/426723/sql-group-by-versus-distinct
GROUP BY is intended for aggregate function use; DISTINCT just removes duplicates (based on all column values matching on a per row basis) from visibility.
If TABLE2 allows duplicate values associated to TABLE1 records, you have to use either option. Depends on the data and what you want to see, but you could use a group by & aggregate function to get the highest (using MAX) or lowest (using MIN) values from TABLE2...
you could try moving the conditions in your 'where' to your joins, though I expect they'll be parsed the same.
If your trying to increase performance, add an index to Table1.Foo and Table2.Type
Have you tried creating an index on the fields you're selecting?
The relative costs of DISTINCT and GROUP BY make sense. One way of (and probably the way it's using) of processing the data is to sort the rows by the fields you provide. Then the difference between the two is that DISTINCT skips rows that are equal to the previous row, and GROUP by happens to run a count using the same metric of equality.