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, Mar 22 '12 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, Mar 22 '12 at 18:17

score 20 · Accepted Answer · answered Mar 22 '12 at 18:12

20

Try this:

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

answered Mar 22 '12 at 18:12

Andrey Gurinov

2,825
1
20
23

This didn't throw any error but it's been over 6 mins. and my query is still executing. Performance is taking a beating. – Niras Mar 22 '12 at 18:19
2

@user583227 - This is the answer either way, If the performance is bad, you should normalize your tables and index them correctly. +1 – Lamak Mar 22 '12 at 18:22
Unfortunately, it will work slow. Because there is no way how to join two tables using concatination efficiently. But it works. – Andrey Gurinov Mar 22 '12 at 18:22
1

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 Mar 22 '12 at 18:26
The no. of rows is in thousands. Null may be an issue I need to look at. Thanks. – Niras Mar 22 '12 at 18:36
3

@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) – Kevin Fairchild Mar 22 '12 at 18:45
While Andrey's answer is correct, it's throwing performance issues. Although they can be handled, but for now running the query to test workability is my concern. Pedro's answer gave me an idea that I can concatenate in the select part of the query, alias it, use the alias in the Join. It worked :)..I don't know either user too. – Niras Mar 22 '12 at 18:50
2

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. – Nick Vaccaro Mar 22 '12 at 19:07
Thanks Norla and Kevin for the clarification. It certainly helped. – Niras Mar 22 '12 at 19:47
1

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. – Niras Mar 22 '12 at 20:22
Glad you got it figured out, @Nidhi – Kevin Fairchild Mar 24 '12 at 02:53

score 1 · Answer 2 · edited May 23 '17 at 11:44

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

SQL Query Concatenate two columns during Inner JOIN

2 Answers2