CODEWITHSUNDEEP
sql
SQL_APPRENTICE
SQL_APPRENTICE

Reputation: 63

SQL-TABLE CREATION

Please explain to me the use of the comma after 'FROM TABLE_ABC A'. how does it work in the execution of the sql query.

CREATE TABLE ABCD AS
( SELECT A.* 
FROM TABLE_ABC A,
(SELECT COL_1,COL_2 FROM
 (SELECT B.*,C.* FROM
TABLE_XYZ B, TABLE_MNO C
WHERE B.COL_X=C.COL_Y
)D
)A.COL_C=D.COL_D
)
WITH DATA PRIMARY INDEX(SASAJS)

Upvotes: 2

Views: 70

Answers (2)

Nelson Teixeira
Nelson Teixeira

Reputation: 6610

You're using a select to create a 2nd table that is also created from another subselect. See it like this and you'll understand it better:

CREATE TABLE ABCD AS(
            SELECT
                A.*
            FROM
                TABLE_ABC A,
                (
                    SELECT
                        COL_1,
                        COL_2
                    FROM
                        (
                            SELECT
                                B.*,
                                C.*
                            FROM
                                TABLE_XYZ B,
                                TABLE_MNO C
                            WHERE
                                B.COL_X = C.COL_Y
                        ) D
                ) 
            WHERE
                A.COL_C = D.COL_D
) WITH DATA PRIMARY INDEX(SASAJS)

but your original code is lacking a WHERE CLAUSE before A.COL_C = D.COL_D. I included it.

I'm assuming that B.* or C.* has a column named COL_D AND A also has it. It would also be better if the D as after the ) before the last WHERE

Upvotes: 0

Maddy
Maddy

Reputation: 927

It is similar to join

select * from #tempA ta join #tempB tb
on ta.ID = tb.ID

same as

select * from #tempA ta, #tempB tb
where ta.ID = tb.ID

Using JOINS makes the code easier to read

Upvotes: 1

Related Questions