cross apply in sql server

recently i was having a problem in my stored procedure, it was working too slow, so one of my colleague suggested cross apply, he said that cross apply work as inner join but without the need of comman key between the table.

my senario is i have three tables crossarticle_article and crossarticle_articletocategory, and crossarticle_article now i want to retreive all the records whose categoryid is 4, categoryid are stored in crossarticle_articletocategory

as much i understood, i created below query for it:

SELECT *
FROM   crossarticle_article c
       CROSS APPLY
crossarticle_articletocategory cc1
CROSS APPLY
crossarticle_category cc2
WHERE  cc2.id = 1

this query should return only records from crossarticle_article开发者_如何学Python where category id matches 1 but it returns all the records from all 3 tables.

where i am going wrong, if this is not the way of using cross apply then how to use, and whats the advantage of it.

is it that cross apply works with same tables... if so than what could be the scenario...

Assuming id is the key field in all tables, The Query should be:

SELECT c.*,
       c1.colXXX,
       c2.colYYY --ALL columns  u need
FROM   crossarticle_article c
       CROSS APPLY(
    SELECT cc1.colXXX
    FROM   crossarticle_articletocategory cc1
    WHERE  cc1.id = c.id
) c1
CROSS APPLY(
    SELECT cc2.colYYY
    FROM   crossarticle_category cc2
    WHERE  cc2.id = c.id
) c2
WHERE  c.id = 4