Question 20554 in Mysql Question posted in
The official MySQL documentation can be found here.

How to join two different tables without using join or union? – Mysql

So I have a table ‘A’ with has column of numerical values of 12 digits, now there is another table ‘B’ which has only first 6 digits of the same numerical value, I want to join both the tables using the first 6 digits of table ‘A’ and already 6 digits of values in table ‘B’.

SELECT *    
FROM A 
inner join B ON A.K1 = B.K1

here K1 is basically the first 6 digits of the number which I created as a new column in output.

I cannot use ALTER as it is a live table.

Tags:

2

Answers


  1. 0 
    Select a.*, b.*
from table_a a
full outer join table_b b on 
  to_number(substr(rtrim(ltrim(to_char(a.col_n))),1,6))=b.col_n;

    That’s what I can think of having as much information as you provided. I do not know whether your business requires that it should be an inner join, left/right outer join or full outer join.
    Union is a completely different concept: that just reunites into a data set similar columns from different data sets. And there is the simple UNION which makes Oracle to sort and eliminate duplicates and UNION ALL, which takes all the data, including duplicates if any.

    Login or Signup to reply.
  2. 0

    You could just join on the first 6 digits then:

    Example data:

    Table test1:

    id somestring
    123456 foo
    234567 bar
    345678 baz

    Table test2:

    id otherstring
    123456789012 eenie
    234567890123 meenie
    987654321098 mo

    The statement

        SELECT * 
      FROM test1
INNER JOIN test2
        ON test1.id = LEFT(test2.id, 6)

    will produce

    id somestring id otherstring
    123456 foo 123456789012 eenie
    234567 bar 234567890123 meenie

    Test it in this db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search