It's the sleazy motel that rents rooms by the hour. The clerk doesn't ask what for, and doesn't want to know.
The No Tell Motel is where philandering affairs and criminal deals take place. Human nature being what it is, that also makes it the site of gruesome unsolved murders. Long story short, if you have something you can drink, smoke, snort, shoot, or fuck but don't have a convenient/affordable place to carry out the activity in question at, you go here to do it.
Low-lifes on the run, prostitutes turning tricks, and the detectives who want to talk to them, will all end up here sooner or later.
See also Smithical Marriage, and Love Hotels for Japan's more glamorous (or cleaner, at least) equivalent. Detectives usually end up here by Going by the Matchbook. May also be a Hell Hotel.
The entire Sin City series starts off in one of these as Marv is framed for murder.
In Highlander, the motel that The Kurgan stays at in New York apparently has at least one door-to-door hooker (Candy). It's neither shown nor explained exactly what he does there. (For anyone else it would be obvious, but this is The Kurgan we're talking about.)
Seen in 12 Monkeys, where Bruce Willis and his psychiatrist visit one to work out just what the hell is going on with their lives in privacy.
When he is released in Psycho II, Norman Bates is shocked to find that this is how the Bates Motel is being run. He promptly sacks the manager, the hilariously sleazy Dennis Franz.
La Habitacion Azul (The Blue Room), a Mexican film, has this in its title, where the manager of the hotel especially rents the eponymous blue room for lovers and their escapades.
Vlad Taltos and Kiera both find one of these immensely useful in Orca. Which fits the trope quite well, considering they are, respectively, an assassin on the run from an organized crime syndicate and the best thief and, secretly, most feared demigoddess in the entire Empire.
L.A. Confidential has two examples - one is El Serrano motel, where Buzz Meeks waits to be snuck out of the USA ( he doesn't make it) and Victory Motel, headquarters of Mobster Squad run by the resident Magnificent Bastard Dudley Smith, where out-of-town gangsters arriving in L. A. are hauled over and persuaded to leave and never come back (usually through applying cut rubber hose).
The Black Dahlia has The Red Arrow, which appears again in White Jazz.
In the Maggody mystery novels, Ruby Bee absolutely refuses to acknowledge that this trope applies to the motel she owns, even though it's an open secret that's what all the passing truckers rent rooms for. The only one who doesn't use it for his trysts is Mayor Jim Bob, who takes his girlfriends to Farberville's No Tell Motel to be farther from his wife.
In one episode, Brass even (kinda sorta, but not really) jokingly says that when people die in motels like these, it's generally not reported for quite a while, because the owners keep renting out the room until the smell of the corpse has gotten bad enough that it can no longer be ignored.
With more hotel-like places, appears in The Bill, such as with the Chandler-McAllister relationship. The one that ended with him raping her on their wedding night and then shooting himself during a Hostage Situation as she was in labour (with him in the room).
In an episode of WKRP in Cincinnati, Arthur Carlsen tries to spice up his marriage by taking his wife back to the hotel they stayed at when they eloped, but since then its aspirations have declined a bit...
Carmen: "Arthur, there's a machine in the bathroom that sells... things."
In an episode of Happy Days, Joanie and Chachi had to stay in one when they took a trip to a Beach Boys concert without permission.
Sam and Dean Winchester of Supernatural check into No Tell Motels together fairly frequently. This does not help their reputation at all.
In an episode of Breaking Bad, Jesse stays at one of these, where he smokes some meth and has sex with a prostitute.
On Boy Meets World, Shawn's father dumps him at one of these when he leaves to chase after Shawn's mom, though it's not seen on screen.
These were Faith's digs during her time in Season 3, pushing her character as The Lad-ette and she even uses it as a one night stand (well two nights) with Xander. The Mayor upgrades her to a condo, but advises to maintain her old place as a cover.
All over the place in Law & Order: SVU. The detectives are always frequenting these places to pick up suspects (and witnesses of the shady variety.)
One features in the Heart song All I Wanna Do Is Make Love to You.
The Panic! at the Disco song "Build God, Then We'll Talk" is about one.
Lupino's hotel from the first Max Payne game, which you have to blast through twice, was of this type.
The Sweet Cuppin' CakesDecemberween special is called "Cactus Coffee and the No Tell Motel". There is, of course, neither a No Tell Motel or cactus coffee involved, but Eh! Steve's mouth does explode the universe, and Ready For Primetime does a tiny, tiny dance. Even Strong Bad is stumped and he created the show.
The motels frequented by Mayor Quimby in The Simpsons. In particular, the "SLeEp-eAZY" Motel that Marge and the kids stay at during the episode "The Cartridge Family", complete with prostitutes at the entrance, coin-operated vibrating beds, and a corpse in the pool. Homer, Marge (and Mayor Quimby) go to one closer to a Japanese style Love Hotel in "Grandpa vs. Sexual Inadequacy" too.
The Family Guy episode "Screwed The Pooch" hits all the notes in this tune: prostitutes, sleazy proprietors, crime, roaches, non-working switches, Murphy beds, insane residents, people on the run, America's Most Wanted, police raids.