Member Avatar for bhakti.thakkar

hi all,

Table 1:

ID | COL1
1 | 1
1 | 13
1 | 64

Table 2:

ID | COL1
1 | 14
1 | 13


Output needed:

ID | COl

1 | 1
1 | 13
1 | 14
1 | 64

How will i accomplish this???

Thanks and regards
Bhakti

  • 3 Contributors
  • 3 Replies
  • 85 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by bhakti.thakkar

Dani AI

Generated

As pointed out, a set-union is the right tool here: it merges the two SELECT results and removes the duplicate 13, producing the list shown by . The JOIN example from is conceptually different and not suitable for this goal — joins match rows across tables (producing paired columns or extra rows) rather than producing a single de-duplicated list. The posted JOIN also used inconsistent table aliases and would raise a syntax error.

Notes and cautions that matter in practice:

  • UNION removes duplicate rows; UNION ALL preserves them and is faster.
  • Both SELECTs must return the same number of columns with compatible data types and collations.
  • ORDER BY applies to the final combined result, not to the individual SELECTs.
  • NULLs compare as equal for the purpose of removing duplicates.
  • If collation or type mismatches occur between tables, explicit casting or COLLATE may be required.

Performance/workflow tips for larger data sets:

  • UNION causes sorting or hashing to deduplicate; for very large inputs, use UNION ALL into a staging table, add appropriate indexes, then dedupe with a single DISTINCT/GROUP BY or a windowed de-duplication step.
  • Alternatively, use EXISTS/NOT EXISTS logic when only rows from one table that do not exist in the other are required.

Official behavior and syntax are documented by Microsoft in the Set Operators (UNION/UNION ALL) documentation: .

Recommended Answers

All 3 Replies

Member Avatar for Jx_Man 
select a.id, a.colname, b.colname from Table1 a join Table2 a on (a.id=b.id) where a.id = '1' order by a.id asc
commented: thanks +1
Member Avatar for ee_rlee

hi, you need to use union

SELECT ID, COL1 FROM Table1
UNION
SELECT ID, COL1 FROM Table2

Member Avatar for bhakti.thakkar

hi, you need to use union

SELECT ID, COL1 FROM Table1
UNION
SELECT ID, COL1 FROM Table2

hi,
thanks for your help. UNION Clause helped me to get the desired result

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.