Cross Join without duplicate combinations

by 
select A.id aid,B.id bid
from A inner join B on a.id <= b.id
union
select B.id,A.id
from A inner join B on b.id < a.id

If you wanted to be more sophisticated:

select distinct
       case when a.id<=b.id then a.id else b.id end id1,
       case when a.id<=b.id then b.id else a.id end id2
from A cross join B

In my little unscientific bake off with tiny tables, the latter was faster. And below, the case expressions written as subqueries.

select distinct
       (select MIN(id) from (select a.id union select b.id)[ ]) id1,
       (select MAX(id) from (select a.id union select b.id)[ ]) id2
from A cross join B

More Related Contents:

  1. Add a column with a default value to an existing table in SQL Server
  2. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. The multi-part identifier could not be bound
  5. Fastest way to update 120 Million records
  6. Is it possible to select sql server data using column ordinal position
  7. How to find current transaction level?
  8. How to insert multiple records and get the identity value?
  9. How can a LEFT OUTER JOIN return more records than exist in the left table?
  10. What’s the best to check if item exist or not: Select Count(ID)OR Exist(…)?
  11. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  12. Dynamic SQL to generate column names?
  13. Extracting hours from a DateTime (SQL Server 2005)
  14. Column name or number of supplied values does not match table definition
  15. SQL Server PIVOT perhaps?
  16. How to find consecutive rows based on the value of a column?
  17. Custom aggregate function (concat) in SQL Server
  18. Should I use SQL_Variant data type?
  19. Script to save varbinary data to disk
  20. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  21. How to select date without time in SQL
  22. What is the internal representation of datetime in sql server?
  23. Eliminate and reduce overlapping date ranges
  24. Cumulative Total in MS Sql server [duplicate]
  25. SQL query to group by day
  26. Export table data from one SQL Server to another
  27. SQL: Aggregating strings together
  28. Distributed transaction error?
  29. SQL, How to Concatenate results?
  30. Determine what user created objects in SQL Server

Leave a Comment