SQL Query Concatenate two columns during Inner JOIN

Question

I have table A and table B with Table A having several columns including A1 and A2. Table B too has several columns. My query requires me to concatenate the values in A1 and A2 and then do an inner join on B1.

Example:

Select * 
From A
INNER JOIN B
ON CONCAT(A1,A2) = B1.

Apparently this is not how it should work. Can someone please give me a hand in this query?

Thanks.

It's not as efficient as linking on ID but if this is what you need then this is what you need. You can't simplify a problem beyond it's most basic requirements! — Blindy
– Blindy, Commented Mar 22, 2012 at 18:11
Well i've placed the sample for concatenation of columns in the most represented systems. but yes MS-SQL was the answer to my question. — Pedro Ferreira
– Pedro Ferreira, Commented Mar 22, 2012 at 18:17

Andrey Gurinov · Accepted Answer · 2012-03-22 18:12:12Z

21

Try this:

Select *  
From A 
INNER JOIN B 
ON A1 + A2 = B1

answered Mar 22, 2012 at 18:12

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Lamak Over a year ago

@user583227 - This is the answer either way, If the performance is bad, you should normalize your tables and index them correctly. +1

Kevin Fairchild Over a year ago

Six minutes seems excessive. How much data is it reading/returning? Also, keep in mind that concatenation may have issues depending on datatype or whether A1 or A2 is null.

Kevin Fairchild Over a year ago

@user583227, why did you accept Pedro's answer versus Andrey? It seems like he more directly answered your question and has the most upvotes. While Pedro's answer looks nicer and provides more details, if in your query B1 was 555ABC, A1 was 555, and A2 was ABC, his answer wouldn't work due to the addition of the space -- while Andrey's would be fine. Just curious if there was additional reasoning behind your choice (I don't know either user)

Nick Vaccaro Over a year ago

I agree with Kevin and Lamak here. Andrey's query isn't causing performance issues. It's the table structure that's causing the issues, not the query. Pedro's answer is definitely very nicely worded, albeit being an answer to a different question.

Niras Over a year ago

Okay. The devil behind the bad performance was that I wasn't running the query on a table but on a view. Hence all the wait. Thanks for pointing me to the right problem guys.

|

Community · Accepted Answer · 2017-05-23 11:44:30Z

Sample taken from

Table Geography

region_name store_name
East    Boston
East    New York
West    Los Angeles
West    San Diego

Example 1: For MySQL/Oracle:

    SELECT CONCAT(region_name,store_name) FROM Geography 
    WHERE store_name = 'Boston';
Result: 'EastBoston'

Example 2: For Oracle:

    SELECT region_name || ' ' || store_name FROM Geography 
    WHERE store_name = 'Boston';
Result: 'East Boston'

Example 3: For SQL Server:

    SELECT region_name + ' ' + store_name FROM Geography 
    WHERE store_name = 'Boston';
Result: 'East Boston'

Starting from this, you can adapt to two tables without much issue. In doubt, use a virtual Table to make things more readable.

If in doubt check this other question which has been answered for more details.

StackOverFlow Similar Question

Collectives™ on Stack Overflow

SQL Query Concatenate two columns during Inner JOIN

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related