sql - PostgreSQL - 合并两个选择查询

Question

我有两个表:

公司和用户

用户表:

    id   |   name   |   cpf     |   phone_number  | company_id

    1    |   Jonh   | 111.11.11 |   1111-1111     |    1

    2    |   Marie  | 222.22.22 |   2222-2222     | 

    3    |   Paul   | 333.33.33 |   3333-3333     |    3

    4    |   Luna   | 444.44.44 |   4444-4444     |    1

    5    |   Leo    | 555.55.55 |   5555-5555     |    

公司表:

id   |   name       |   cnpj      |   phone_number | company_data | consumer

1    |  companyA    | 111.1111.11 |   1111-1111    |     data1    | true

2    |  companyB    | 222.2222.22 |   2222-2222    |     data2    | true

3    |  companyC    | 333.3333.33 |   3333-3333    |     data3    | false

我想选择 company_id IS NULL 的所有用户和所有公司其中 consumer 为真

我想做的是这样的:

 Select u.name as name,
          u.cpf as document,
          u.phone_number as phoneNumber,
          'false' as company
          FROM users u 
          WHERE company_id is NULL

   UNION

   Select c.name as name,
          c.cnpj as document, 
          c.phone_number as phoneNumber,
          c.company_data as companyData
          'true' as company
          FROM company c
          WHERE c.consumer = 'true'

  ORDER BY id

我想要的答案是:

id   |   name       |   document  |   phone_number  | companyData |  company

1    |  companyA    | 111.1111.11 |   1111-1111     |   data 1    |   true

2    |  companyB    | 222.2222.22 |   2222-2222     |   data 2    |   true

2    |   Marie      | 222.22.22   |   2222-2222     |             |  false

5    |   Leo        | 555.55.55   |   5555-5555     |             |  false

我可以接受列 cpf 和 cnpj 分隔的答案，如果它不适用于所选实体，则结果为 null。这样我就不需要 company 列

Answer 1

两个选择需要相同数量的列，只需添加一个 NULL 列(您可能必须将其转换为数据类型):

   Select u.name as name,
          u.cpf as document,
          u.phone_number as phoneNumber,
          CAST(NULL AS VARCHAR(20)) as companyData,
          'false' as company
          FROM users u 
          WHERE company_id is NULL

   UNION

   Select c.name as name,
          c.cnpj as document, 
          c.phone_number as phoneNumber,
          c.company_data as companyData
          'true' as company
          FROM company c
          WHERE c.consumer = 'true'

  ORDER BY id