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.

Solution 1

Try this:

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

Solution 2

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

Comments

[Niras]

This didn't throw any error but it's been over 6 mins. and my query is still executing. Performance is taking a beating.

[Lamak]

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

[Andrey Gurinov]

Unfortunately, it will work slow. Because there is no way how to join two tables using concatination efficiently. But it works.

[Kevin Fairchild]

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.

[Niras]

The no. of rows is in thousands. Null may be an issue I need to look at. Thanks.

[Kevin Fairchild]

@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)

[Niras]

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.

[Nick Vaccaro]

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]

Thanks Norla and Kevin for the clarification. It certainly helped.

[Niras]

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.

[Kevin Fairchild]

Glad you got it figured out, @Nidhi