SQL query symbol "*="

Question

What does the symbol "*=" stand for in a SELECT statement? How does this affect the performance? Can we replace it with JOINS?

Thanks STR

Answer 1

In some implementations of SQL, a SELECT statement that has a assignment operator(=) can be used to create the relationship between a column heading and the expression that defines the values for the column.

So an example might be:

SELECT name = 'renamed_column_name'
FROM users

More Info:

Unfortunately the = operator can mean both assign and equality.

For assignment:

DECLARE @Counter INT;
SET @Counter = 1;

For equality:

The = is a equality operator states that the left side must equal the right side.

The could mean a value must equal the result returned by a subquery, or a variable must equal a literal, no matter the case... a = b means that a and b have to have the same value.

SELECT * FROM users LEFT JOIN posts ON users.id = posts.user_id

Answer 2

In some database systems,

SELECT ... FROM a, b WHERE a.field1 *= b.field2

is an old syntax for a LEFT OUTER JOIN:

SELECT ... FROM a LEFT JOIN b ON a.field1 = b.field2

Microsoft SQL Server, for example, considers the *= syntax to be deprecated since version 2005.

So yes, you can replace *= with a JOIN. In fact, you should. (I doubt that it affects performance in any relevant way, but your queries might stop working in newer versions of your database engine.)