Adding column to table and automatically UNION tables afterwards-CodePudding

I have the following problem:

I have a table with distinct pairs of ID and Indicator.

    Table A:
    ID         Indicator        
    ----------------------
    1            1       
    1            2         
    1            3          
    2            1     
    2            2             
    2            3     
    ----------------------

And a second table with distinct names.

    Table B:
    Names                 
    -------
    John                                   
    Lea                      
    Peter                                      
    --------

What I want is a column in table A with the names of Table B for every ID-Indicator pair:

    Outcome:
    ID         Indicator    Names    
    ----------------------------------
    1            1          John
    1            2          John         
    1            3          John          
    2            1          John     
    2            2          John             
    2            3          John     
    1            1          Lea
    1            2          Lea         
    1            3          Lea          
    2            1          Lea     
    2            2          Lea             
    2            3          Lea  
    1            1          Peter
    1            2          Peter         
    1            3          Peter          
    2            1          Peter     
    2            2          Peter             
    2            3          Peter  
    ----------------------------------

In reality Table B, with the names, is really long, so a manual solution like:

SELECT ID, Indicator, Names = 'John'
FROM TableA
UNION
SELECT ID, Indicator, Names = 'Lea'
FROM TableA

Is not feasible. Is there a non-manual solution to this problem? Any help is highly appreciated!

CodePudding user response：

You want to join each row in TableA with each row in TableB, this is known as a cross join, as commented. The number of rows output will be A * B.

If you want all columns from both tables the syntax is simply

select * 
from TableA cross join TableB