Iterating multiple times over same table (postgres sql)

i am working with sql from few days as beginner and stuck at a problem!

Problem:

• Given A table user_email_table which columns are (id, user_id, user_id_email)
• How to get all user co-related to each and every user extending himself

user_email_table

id | user_id | email_id
1  |    1    |    xyz
2  |    2    |    xyz2
3  |    3    |    xyz3
4  |    4    |    xyz4

Desired Output

id  | user_id_1 | user_id_2 | user_id_1_email | user_id_2_email |
-----------------------------
1   |      1    |     2     | xyz|xyz2|
1   |      1    |     3     |xyz|xyz3|
1   |      1    |     4     |xyz|xyz4|
1   |      2    |     1     |xyz2|xyz1|
1   |      2    |     3     |xyz2|xyz3|
1   |      2    |     4     |xyz2|xyz4|
1   |      3    |     1     |xyz3|xyz1|
1   |      3    |     2     |xyz3|xyz2|
1   |      3    |     4     |xyz3|xyz4|
1   |      4    |     1     |xy4|xyz1|
1   |      4    |     2     |xyz4|xyz2|
1   |      4    |     3     |xyz4|xyz3|

Please Ignore validate data of email fields This are just for reminding to mention these columns in output-table

What SQL query can result into this table

Answer

You want a “cross join” of the table against itself excluding the self-matching rows. You can do:

select
  1 as id,
  a.user_id as user_id_1,
  b.user_id as user_id_2,
  a.email_id as user_id_1_email,  
  b.email_id as user_id_2_email
from user_email_table a
cross join user_email_table b
where a.user_id <> b.user_id

EDIT:

As @IMSoP points out the query can also use a common join with a join predicate and can be rephrased as:

select
  1 as id,
  a.user_id as user_id_1,
  b.user_id as user_id_2,
  a.email_id as user_id_1_email,  
  b.email_id as user_id_2_email
from user_email_table a
join user_email_table b on a.user_id <> b.user_id