Can I select before join?

I want to left join two tables. My problem is that the table from the "right" does not exist in the database. I need to create it during the query. In more details, the "right" table is obtained by extraction (select ... where ...) of certain rows from 开发者_JS百科an existing table.

I thought that the solution would be to join first and then to select. I thought that it will be just slower than elect and join but than I realized that it will not work at all.

For example:

Left table:

| Dog | 1 |
| Cat | 0 |

Right table:

| Dog | Paul | yes |
| Cat | Carin | no |

I want to use only those rows from the second table where the second column has "Carin". So, if I join after selection of those rows I get:

| Dog | 1 | Null | Null|
| Cat | 0 | Carin | no |

But if I first join, I get:

| Dog | 1 | Paul | yes |
| Cat | 0 | Carin | no |

And then if I select rows with Carin I get:

| Cat | 0 | Carin | no |

So, you see that result is different. In the first case I get 2 rows and in the second case I got just 1 row.

So, is it possible to select before join?

I think you're talking about referencing fields created in a subquery defined as part of a join statement. Something like this:

SELECT a.id, b.name 
   FROM table AS a
   LEFT JOIN
     (SELECT name FROM table2) AS b
   ON a.id = b.id

In this, you're referencing b.name even though b.name only exists as part of the subquery created later in the query.

If this is what you mean, then yes you absolutely can do that. There would be very little reason to join on a subquery if you couldn't.

If I'm mistaken about your intent, please add the actual query you'd like to run to your question so I can see the problem more clearly.

You can also join on a constant. Basically, you move the expression in the WHERE clause to the ON clause:

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet AND b.name='Carin';

The example from Outis did not work for me, but this worked when I replaced the AND by a WHERE.

SELECT a.pet, a.neutered, b.name, b.allergic
  FROM a
  LEFT JOIN b
    ON a.pet=b.pet WHERE b.name='Carin';