Difference between inline view and WITH clause?

There are some important differences between inline views (derived tables) and WITH clause(CTE) in Oracle. Some of them are quite universal, i.e. are applicable to other RDBMS.

1. WITH can be used to build recursive subqueries, inline view -not (as far as I know the same is for all RDBMS that support CTE)
2. Subquery in WITH clause is more likely be physically executed first ; in many cases, choosing between WITH and inline view makes optimizer to choose different execution plans (I guess it's vendor specific, maybe even version specific ).
3. Subquery in WITH can be materialized as a temporary table ( I'm not aware if any other vendor but Oracle supports this feature).
4. Subquery in WITH can be referenced multiple times , in other subqueries, and in the main query (true for most RDBMS).

Other answers cover the syntax differences pretty well so I won't go into that. Instead this answer will just cover performance in Oracle.

The Oracle optimizer may choose to materialize the results of a CTE into an internal temporary table. It uses a heuristic to do this instead of cost-based optimization. The heuristic is something like "Materialize the CTE if it isn't a trivial expression and the CTE is referenced more than once in the query". There are some queries for which the materialization will improve performance. There are some queries for which the materialization will dramatically degrade performance. The following example is a bit contrived but it illustrates the point well:

First create a table with a primary key that contains integers from 1 to 10000:

CREATE TABLE N_10000 (NUM_ID INTEGER NOT NULL, PRIMARY KEY (NUM_ID));

INSERT /*+APPEND */ INTO N_10000
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10000
ORDER BY LEVEL;

COMMIT;

Consider the following query that uses two derived tables:

SELECT t1.NUM_ID
FROM 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t1
LEFT OUTER JOIN 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

We can look at this query and quickly determine that it won't return any rows. Oracle should be able to use the index to determine that as well. On my machine the query finishes nearly instantaneously with the following plan:

I don't like repeating myself, so let's try the same query with a CTE:

WITH N_10000_CTE AS (
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Here is the plan:

That's a really bad plan. Instead of using the index, Oracle materializes 10000 X 10000 = 100000000 rows into a temp table only to eventually return 0 rows. The cost of this plan is around 6 M which is much higher than the other query. The query took 68 seconds to finish on my machine.

Note that the query could have failed if there isn't enough memory or free space in the temp tablespace.

I can use the undocumented INLINE hint to disallow the optimizer from materializing the CTE:

WITH N_10000_CTE AS (
  SELECT /*+ INLINE */ n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

That query is able to use the index and finishes almost instantly. The cost of the query is the same as before, 11. So for the second query, the heuristic used by Oracle resulted it in it picking a query with an estimated cost of 6 M instead of a query with an estimated cost of 11.