CODEWITHSUNDEEP
oracle-database
HJW
HJW

Reputation: 23443

Order of columns returned in SQL query (With table join)

I am wondering what determines the order of columns returned in a SQL query.

For example, SELECT * FROM SOMETABLE;

SQ_ID   |BUS_TYPE   |VOIP     |LOCAL_PHONE
--------|-----------|---------|-------------
SQ000001|Business   |Y        |N

I am guessing the attribute COLUMN_ID determines this. In the case of a table join, for example, SELECT * FROM SOMETABLE LEFT JOIN OTHERTABLE USING (SOME_COL); how is the order now determine.

Upvotes: 1

Views: 3353

Answers (2)

APC
APC

Reputation: 146239

The order of columns in SELECT * FROM some_table is determined by the column_id of each column, as seen in USER_TAB_COLUMNS.

The order of columns in SELECT * FROM some_table JOIN other_table is all the columns for each table starting with the leftmost table after the FROM clause. In other words, this ...

SELECT * FROM some_table JOIN other_table

... is equivalent to this ...

SELECT some_table.*, other_table.* FROM some_table JOIN other_table

Changing that inner join to LEFT JOIN or RIGHT JOIN won't change the projection.

This is, of course, theoretical. We should never use select * in production code. Explicit column declarations, with table aliases when joining, are always safer. Apart from better expression of intent, explicit projections protect our code from future changes to the tables such as adding a LOB column or a column name which creates ambiguity with a joined table's column.

Upvotes: 2

Marc Fischer
Marc Fischer

Reputation: 1306

You can list the order of the colums in the Select statement:

SELECT SOME_COL, SOME_OTHER_COL
FROM SOMETABLE LEFT JOIN OTHERTABLE USING (SOME_COL)

But you also speak of the ID influencing the order and of ordering in general. So I think you could also be looking for ORDER BY to order the rows:

SELECT *
FROM SOMETABLE LEFT JOIN OTHERTABLE USING (SOME_COL)
ORDER BY SOME_COL

What also comes quite handy in this case is the use of aliases. Especally when both tables have coloums with the same name:

SELECT s.some_col, o.some_col
FROM SOMETABLE s LEFT JOIN OTHERTABLE o ON(o.id = s.id)
ORDER BY o.SOME_COL

I use the ON JOIN syntax in this case, because i find this more intuive when using aliases but it should also work with USING.

Upvotes: 1

Related Questions