CAST

Hook: Interviewers love CAST because it reveals whether you can turn messy data into the right type without breaking filters or slowing a query.

Question: What does CAST do in SQL?

Answer: CAST explicitly converts a value from one data type to another, like text to number, text to date, or number to text. You use it when SQL needs the value in a different shape for math, comparison, sorting, or display. If the value cannot be converted, most databases raise an error unless you use a safer alternative such as TRY_CAST or SAFE_CAST.

Interview-Ready Answer: I use CAST when I want to explicitly convert a value to another SQL type, for example turning a string into an integer for filtering or a number into text for output. I prefer it over implicit conversion because I control the target type and avoid surprises. One thing I watch for is invalid data: CAST('abc' AS INTEGER) fails in most databases, so for dirty input I clean first or use a safe cast variant where the database supports it.

What CAST really is

Detailed Explanation: CAST tells the database to turn one value into another type on purpose. It is the explicit version of conversion: you are saying, 'treat this as an integer, date, decimal, or text'. That matters in SELECT, WHERE, JOIN, ORDER BY, and HAVING because type mismatches can change results or break a query.

How it works under the hood

1. The parser reads the source expression and the target type, such as INTEGER, DATE, or VARCHAR(20).
2. The engine checks that a conversion rule exists between the source and target types. If no conversion is defined, the query fails.
3. If the expression is a constant, the optimizer may fold it early; if it is a column, the cast is applied row by row during execution.
4. The engine applies the conversion rules. For example, text must look like a valid number or date, and character types may need a length, while decimals may need precision and scale.
5. If the value cannot be represented in the target type, most engines raise an error. NULL stays NULL.

When and why to use it

• Convert text to numeric for math or filtering, like turning '1001' into 1001.
• Convert dates stored as text into real date types so comparisons work correctly.
• Convert numbers to text for display, labels, or concatenation.
• Make both sides of a join the same type so the comparison is predictable.

CAST vs alternatives

Some dialects also support CONVERT, but CAST is the standard, portable form. The big idea is simple: CAST makes the type change visible instead of leaving it to the database to guess.

Performance and edge cases

One cast is O(1) for a single value. On a query touching n rows, the cost is usually O(n) because the conversion happens per row. That cost is often small, but a cast in a WHERE clause can block index use when the column itself is wrapped in the function, turning a fast seek into a full scan. A common fix is to cast the literal or parameter to the column's type, not the other way around.

Memory use is usually O(1) extra for the cast itself, but a later sort or hash join may still spill if the query becomes large. Also, do not rely on default lengths or precision: always specify what you mean, like VARCHAR(20) or DECIMAL(10,2), because engines differ in how they handle omitted details.

Memory hook for the whole topic: the value is the same box, but CAST changes the label on the box so the database knows how to use it.

Real-World Example: Imagine a checkout service that imports daily orders from a legacy partner file. Their order_id arrives as text, but the warehouse expects integers and the revenue report expects decimals. The ETL job uses CAST to normalize '1001' into 1001 and '19.95' into a numeric amount before loading the table.

What goes wrong when people misunderstand CAST? A partner sends 'N/A' in a numeric column, the query tries to convert it, and the nightly reconciliation job fails halfway through. The symptom is a failed batch, retry loops in the scheduler, and dashboards showing missing totals or stale numbers. In the logs you usually see a conversion error from the database engine, and customer support notices that orders from the last import never appeared in finance reports.

The practical lesson is: if input can be dirty, guard the cast or use a safe cast form. If you already know the data is clean, CAST is the most direct and readable choice.

Follow-up & Tricky Questions:

• When should I cast the literal instead of the column? Cast the side that preserves indexes and keeps the intent clear. If the column is already numeric, compare it to a numeric literal instead of casting the column to text.
• What happens if the value cannot be converted? In most databases, CAST raises an error and the query fails. If you need to survive dirty input, use TRY_CAST or SAFE_CAST where available.
• Can I use CAST in a join? Yes, but be careful: casting join keys can make the join slower and can hide data-quality problems. It is usually better to store both columns in the same type.
• Why does a cast in WHERE sometimes make the query slow? Because the engine may need to convert every row before filtering, which can prevent an index seek. That turns a selective lookup into a scan.
• How is CAST different from CONVERT? CAST is the standard SQL form. CONVERT is vendor-specific in many systems and sometimes adds style or format options.
• Does CAST on NULL fail? No, CAST(NULL AS INTEGER) still returns NULL. Missing data stays missing; only bad non-null data causes the conversion error.
• Does CAST always round numbers? Do not assume that. Conversion rules depend on the target type and the database, so for interviews say that exact numeric behavior varies and should be checked in the engine you use.
• Can the optimizer save me from a bad cast? Not reliably. If the cast is reachable during evaluation, invalid data can still error out, so you should sanitize first or use a safe cast form.

Common Mistakes:

• Casting the indexed column in WHERE. Correction: cast the literal or parameter instead, so the database can still use the index.
• Forgetting length or precision. Correction: write the full target type, like VARCHAR(20) or DECIMAL(10,2), instead of relying on defaults.
• Assuming bad text becomes NULL. Correction: plain CAST usually errors; only safe variants return NULL on failure.
• Using CAST as a data-cleaning shortcut. Correction: it converts valid data, but it does not fix invalid values; clean or validate first.

Memory Hook: CAST is like putting the same object into a different labeled box: the thing is unchanged, but the database now knows how to treat it.

Cheat Sheet:

• CAST = explicit type conversion.
• Use it for text-to-number, text-to-date, or number-to-text.
• Bad input usually errors; NULL stays NULL.
• Be careful in WHERE and JOIN clauses because casts can hurt index use.
• Specify exact target types, especially length, precision, and scale.
• For dirty data, prefer TRY_CAST or SAFE_CAST if your database supports them.

Practice Tasks:

• Cast a text column containing prices like '12.50' into DECIMAL(10,2) and sort by the numeric value.
• Write a query that filters rows where a text order ID equals 1001 without casting the indexed column.
• Add a safe cast around a dirty date column so valid rows load and invalid rows are skipped or set to NULL.