I have a table that contains the following columns:

1. Master ID

The Master ID can be shared between different rows with different IDs.

E.g.:

ID | Master ID
1  |  1
2  |  1
3  |  1
4  |  2

Knowing the ID I want to retrieve all the rows that share the same master ID

I managed to do it using this query:

Select *
FROM table t
LEFT JOIN table t2
ON t.MASTER_ID = t2.MASTER_ID
Where t.ID = '1'

Then I also tried using:

Select *
FROM table t
LEFT JOIN table t2
ON t.MASTER_ID = t2.MASTER_ID and t.ID = '1'

In that case, it was much slower. Can anyone explain why?

The queries are doing different things, the first you are saying:

1. give me all rows from `table` where `id = 1`
2. Also give me rows from t2 with a matching master ID

In the second you are saying

1. Give me all rows from `table`
2. Return rows from `t2` with a matching master ID and where `t1.ID = 1`

In a simple example you might have

ID  Master_ID
------------------------
1   1
2   1
3   1
4   2

So your first query will return:

t1.ID   t1.Master_ID    t2.ID   t2.Master_ID
--------------------------------------------
1           1           1       1
1           1           2       1
1           1           3       1

Your second query will return

t1.ID   t1.Master_ID    t2.ID   t2.Master_ID
--------------------------------------------
1           1           1       1
1           1           2       1
1           1           3       1
2           1           NULL    NULL
3           1           NULL    NULL
4           2           NULL    NULL

So basically in the first query you are returning a limited number of rows from your table, whereas in the second you return all rows, but only join to some of them.