MySQL IS IN() AS A RESULT

Question

How do I display in SELECT results 1 if a variable exits in other table and 0 if not? Is it possible or I have to JOIN? And in case it is possible only by JOIN, what if my SELECT is really complicated and I want to LIMIT it before JOINING?

Lets say that table 1 and table 2 contain column named pid. Would like to select * from table 1 , limit it to 100 results (limit 100), and add one column to results determinating if a pid of a result in table 1 is in table 2.

Answer 1

Try @peterm solution and if you want to check more conditions you can use CASE statement

SELECT t1.*, 
case when t2.pid IS NULL then 0 else 1 end as exists_in_table2
  FROM
(
  SELECT *
    FROM table1
   ORDER BY pid
   LIMIT 100
) t1 LEFT JOIN table2 t2
    ON t1.pid = t2.pid

This is the same as @peterm told, I just changed the checking with CASE statement and the SQLFIDDLE

Answer 2

Try

SELECT t1.*, (t2.pid IS NOT NULL) exists_in_table2
  FROM
(
  SELECT *
    FROM table1
   ORDER BY pid
   LIMIT 100
) t1 LEFT JOIN table2 t2
    ON t1.pid = t2.pid

Here is SQLFiddle demo