How to join on concatenated columns SQL

Question

I have two tables,

A segment file

INVNO  SEGNO  COLUMNS
00001  1      Blah
00001  2      Blah
00001  3      Blah
00002  1      Blah
00002  2      Blah
00003  1      Blah

and a details file

INVNO  SEGNO   COLUMNS
00001  1       Blah
00001  2       Blah
00001  2       Blah
00002  1       Blah
00003  1       Blah
00003  1       Blah

So, I need everything from both tables. What is the best practice here? My initial thought is to someone do a join on a concatenated column (concatenating INVONO & SEGNO) in both tables. I don't know how to do that but that would be my best guess. Any and all help is appreciated.

Possible duplicate of [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — Standin.Wolf, Nov 04 '19 at 21:03

score 3 · Answer 1 · answered Nov 04 '19 at 20:59

3

Just join it like this. I would spell out all the columns and not use a wild card. But you did not tell us the columns.

select s.invno, s.segno, s.*, d.*
from segment s
join details d on s.invno = d.invno and s.segno = d.segno

answered Nov 04 '19 at 20:59

Hogan

69,564
10
76
117

score 2 · Answer 2 · answered Nov 04 '19 at 20:58

2

You can join on two columns:

select . . .   -- your columns go here
from segments s join
     details d
     on d.invno = s.invno and d.segno = s.segno

answered Nov 04 '19 at 20:58

Gordon Linoff

1,242,037
58
646
786

SUMguy · Accepted Answer · 2019-11-04T21:40:45.653

1

Use multiple columns.

select s.invno, s.segno, s.*, d.*
from segments s join
     details d
     on d.invno = s.invno AND d.segno = s.segno

edited Nov 04 '19 at 21:40

answered Nov 04 '19 at 21:14

SUMguy

1,505
4
31
61

This is wrong -- if these are character strings it will have "false joins" eg 20 + 220 will join to 202+20. If these are number then it will just fail. You should never do a concatenation on a join instead use the AND operator. It is faster AND more correct – Hogan Nov 04 '19 at 21:30
@Hogan is correct, AND operator is better in most circumstance so I have edited my post to reflect the correct answer, sorry that was my bad – SUMguy Nov 04 '19 at 21:37

How to join on concatenated columns SQL

3 Answers3