SQL would using between statement improve this?

Question

I want to find out using a select statement what columns in a table share similar information.

Example: Classes table with ClassID, ClassName, ClassCode, ClassDescription columns.

This was part of my SQL class that I already turned in. The question asked "What classes are part of the English department?"

I used this Select statement:

SELECT * 
FROM Classes
WHERE ClassName LIKE "English%" OR ClassCode LIKE "ENG%"

Granted we have only input one actual English course in this database, the end result was it executed fine and displayed everything for just the English class. Which I thought was a success since we did populate other non English courses in the database.

Anyways, I was told I should have used a BETWEEN statement.

I am just sitting here thinking they would both do what I needed them to do right?

I'm using SQL Server 2014

I wouldn't advise using a `BETWEEN` for text comparisons (if that would even work), but I would have `LOWER`ed or `UPPER`ed the column for the comparison to make it case insensitive (unless you have constraints that prevent different cases). Are you certain they didn't mean an infix comparison (`LIKE '%English%'`, note the first `%`)? — jpmc26, Nov 02 '15 at 05:08

score 1 · Answer 1 · edited May 23 '17 at 10:27

No, BETWEEN would probably be a bad idea here. BETWEEN doesn't allow wildcards and doesn't do any pattern matching in any RDBMS I've used. So you'd have to say BETWEEN 'ENG' AND 'English'. Except that doesn't return things like 'English I' (which would be after 'English' in a sorted list).

It would also potentially include something like 'Engineering' or 'Engaging Artistry', but that's a weakness of your existing query, too, since LIKE 'ENG%' matches those.

If you happen to be using a case-sensitive collation you add a whole new dimension of complexity. Your BETWEEN statement gets even more confusing. Just know that capital letters generally come before lower case letters, so 'ENGRAVING I' would be included but 'Engraving I' would not. Additionally, 'eng' would not be included. Note that case-insensitive collation is the default.

Also whats the difference when searching for null values in one table and one column
column_name =''
or
column_name IS NULL

You're not understanding the difference between an empty string and null.

An empty string is explicit. It says "This field has a known value and it is a string of zero length."

A null string is imprecise. It means "unknown". It could mean "This value wasn't asked for," or "This value was not available," or "This value has not yet been determined," or "This values does not make sense for this record."

"What is this person's middle name?"

"He doesn't have one. See, his birth certificate has no middle name listed." --> Empty string

"I don't know. He never told me and I don't have any birth or identity record." --> NULL

Note that Oracle, due to backwards compatibility, treats empty strings as NULLs. This is explicitly against ANSI SQL, but since Oracle is that old and that's how it's always worked that's how it will continue to work.

Another way to look at it is the example I tend to use with numbers. The difference between 0 and NULL is the difference between having a bank account with $0 balance and not having a bank account at all.

score 0 · Answer 2 · answered Nov 02 '15 at 07:19

Nothing can be said unless we see table and its data.Though don't use between.

Secondly first find which of the column is not null by design.Say for example ClassName cannot be null then there is no use using ClassCode LIKE "ENG%",just ClassName LIKE "English%" is enough,similarly vice versa is also true.

Thirdly you should use same parameter in both column.for example

ClassName LIKE "English%" OR ClassCode LIKE "English%"

see the difference.

score -1 · Answer 3 · edited Nov 02 '15 at 06:09

-1

Select * FROM Classes
    Where ClassName LIKE "%English%"

edited Nov 02 '15 at 06:09

Pரதீப்

91,748
19
131
172

answered Nov 02 '15 at 05:21

samarth

87
1
1
7

5

@Webruster It does no such thing. `SELECT *` is poor practice because it makes the database have to look up the table definition and enumerate the columns. It also encourages developers to be lazy and not ask for the data they need, causing the database, network, and application to have to fetch and store data that will never be used. – Bacon Bits Nov 02 '15 at 05:29
@Webruster SQL injection is where someone provides input that contains a SQL query and their query gets executed because of improper SQL statement construction (e.g. a bit of code that does `sql = "SELECT * FROM my_table WHERE name ='" + requested_name + "'"` and the user inputs `name'; SELECT * FROM my_table WHERE 'a' = 'a`). SQL injection comes from poor or lack of input sanitizing; using `SELECT *` has nothing to do with it. Bacon hit the nail on the head, though. Databases can optimize better with fewer columns; index only scans are great examples. – jpmc26 Nov 02 '15 at 06:22
1

As a DBA, SELECT * annoys me most because it makes query tuning and indexing virtually impossible. You can't optimise statements with indexes if they are selecting all the columns. – steoleary Nov 02 '15 at 12:33
@steoleary While I recognize the opportunities for improved performance, I think that vastly overstates the case. In my experience, index only scans are exceedingly rare. On top of that, an index is far more often more efficient primarily because it eliminates a vast quantity of rows from consideration for the result set (in other words, avoids a full table scan), so it is usually based on the `WHERE` clauses or the `JOIN` `ON` clauses. Even my most complex queries often use only a couple of indexes. – jpmc26 Nov 10 '15 at 01:10
@jpmc26 it depends on the app, I work on systems where pretty much every query hits a covering index, and those queries fit nicely with filtered indexes, so they are super fast, only looking at exactly which columns they need from a small result set. I also work with apps where they have very wide tables with millions of rows, this is where select * is really bad, especially where there is LOB data in the table as even if the query doesn't reference it, it is still read. – steoleary Nov 10 '15 at 14:17

SQL would using between statement improve this?

3 Answers3