CODEWITHSUNDEEP
sqlpostgresql
robincEPtion
robincEPtion

Reputation: 47

How records are being sorted while using join in psql?

How records are being sorted while using join in psql?

In some cases seems like sorted by any one of the column otherwise, seems like sorting by order in which it maches our query after ON . but not sure how its done. Especially while using right and left join Simply, What is the nature of displaying records?

Upvotes: 2

Views: 3839

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

In general there is no internal order to the records in a SQL table. From the Postgres documentation on ORDER BY:

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

If you want to have a certain order in your result set you need to specify one using ORDER BY. For example, if you were joining two tables A and B you could use:

select
    a.col, a.col2, b.col3
from A a
inner join B b
    on a.col = b.col
order by
    a.col2      -- or replace with whatever column/logic you want to use

Even if there appears to be some order to your current query, you should not rely on it. Adding more data, adding a column, or doing a vacuum all could cause this "order" to change.

Upvotes: 4

Related Questions